Re: how to speed up query - Mailing list pgsql-general

> This whole operation looks contradictory in several ways.
>
> firma1.rid references firma1.dok on (dokumnr)
> Therefore, referential integrity commands that there be NO rows in
> firma1.rid with a dokumnr not present in firma1.dok.
> Therefore your DELETE cannot possibly be deleting anything. It is
> nonsensical:
>   delete from firma1.rid where dokumnr not in (select dokumnr from
> firma1.dok)

Yes, it is nonsensial. However, this command should run fast even if it is
nonsensial.

I my application I add foreign key after running this delete command.
I displayed the table structure after addind, I'm sorry.

I tried the following command

alter table firma1.rid drop constraint rid_dokumnr_fkey;
set constraints all deferred;
explain analyze delete from firma1.rid where dokumnr not in (select dokumnr
from firma1.dok)

but it still produces plan

"Seq Scan on rid  (cost=7703.59..98570208.00 rows=101210 width=6)"
"  Filter: (NOT (subplan))"
"  SubPlan"
"    ->  Materialize  (cost=7703.59..8537.22 rows=55963 width=4)"
"          ->  Seq Scan on dok  (cost=0.00..7373.63 rows=55963 width=4)"


> Did you mean:
>   delete from firma1.dok where dokumnr not in (select dokumnr from
> firma1.rid)
> ??

No. I mean

 delete from firma1.rid where dokumnr not in (select dokumnr from
 firma1.dok)

> The next weird thing:
> I see no UNIQUE index (or primary key) ON firma1.dok.dokumnr. As it is
> being referenced by foreign key constraint from firma1.rid, the system
> would require that.
> This index makes no sense at all:
>   CREATE INDEX dok_dokumnr_idx  ON firma1.dok  USING btree
> (dokumnr);

I listed table structure and constraints partially.
Theis is also primary key constraint in dok table:

 CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),

> Either your problem description is messed up or your postgres
> installation is. My money is on the former.
>
>
> Aside from that, my ideas would be (assuming that you got the
> statement backwards):
> 1.) Try to make the foreign key DEFERRABLE INITIALLY DEFERRED (instead
> of INITIALLY IMMEDIATE), because every delete on firma1.dok CASCADES
> to firma1.rid.

I delete from firma1.rid table.
I dropped the foreign key using

alter table firma1.rid drop constraint rid_dokumnr_fkey;

but the problem persist.

> 2.) Add a DISTINCT clause:
>   delete from firma1.dok where dokumnr not in (select DISTINCT
> dokumnr from firma1.rid)

I tried
   delete from firma1.rid  where dokumnr not in (select DISTINCT
 dokumnr from firma1.dok)

but this runs still very long time.

output from explain:

"Seq Scan on rid  (cost=20569.69..98583074.10 rows=101210 width=6)"
"  Filter: (NOT (subplan))"
"  SubPlan"
"    ->  Materialize  (cost=20569.69..21403.32 rows=55963 width=4)"
"          ->  Unique  (cost=0.00..20239.73 rows=55963 width=4)"
"                ->  Index Scan using dok_dokumnr_idx on dok
(cost=0.00..20099.82 rows=55963 width=4)"


Andrus.