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

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

>For future reference, I beleive the problem is the NOT IN. It has this
>"feature" where if any of the rows it searches has a NULL, it will
>return FALSE for *all* rows. So the whole table has to be scanned to
>check that there arn't any NULLs, before a single row can be returned.
>This is why it can't be converted to a join.

Thank you.
As I understand, only way to optimize the statement

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

assuming that  firma1.dok.dokumnr does not contain null values is to change
it to

CREATE TEMP TABLE mydel AS
 SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL;
DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
drop table mydel;


I run the following commands (first number of minutes from script start) in
my script:

18 Duration 2,9 minutes: ALTER TABLE dok ADD PRIMARY KEY (dokumnr)
...
81 Duration 9,6 minutes:  CREATE INDEX rid_dokumnr_idx ON rid (dokumnr)
...
101 Duration 10,5 minutes:  analyze
...
113  Duration 11 minutes: CREATE TEMP TABLE mydel AS
 SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL

122 Duration 9,6 minutes:  DELETE FROM rid USING mydel WHERE rid.dokumnr
=mydel.dokumnr

133 Duration 11 minutes:  ALTER TABLE rid ADD FOREIGN KEY (dokumnr)
REFERENCES dok
  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE

When I run command

CREATE TEMP TABLE mydel AS
 SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL

from pgAdmin, it takes 1 second.
When I run this command from script it takes 11 minutes!

Any idea why running this command from script takes 11 minutes?

I have created
indexes on dok and rid and ran analyze before using CREATE TEMP TABLE

So I expect that CREATE TEMP TABLE command must take same time to run from
script and from pgAdmin.

My script in running in single transaction.
Should I use commit after index creation or after ANALYZE command?


In pgAdmin

explain analyze  SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL

returns

"Hash Left Join  (cost=7760.27..31738.02 rows=1 width=4) (actual
time=2520.904..2520.904 rows=0 loops=1)"
"  Hash Cond: (r.dokumnr = d.dokumnr)"
"  Filter: (d.dokumnr IS NULL)"
"  ->  Seq Scan on rid r  (cost=0.00..17424.24 rows=202424 width=4) (actual
time=0.032..352.225 rows=202421 loops=1)"
"  ->  Hash  (cost=6785.01..6785.01 rows=56101 width=4) (actual
time=211.150..211.150 rows=56079 loops=1)"
"        ->  Seq Scan on dok d  (cost=0.00..6785.01 rows=56101 width=4)
(actual time=0.021..147.805 rows=56079 loops=1)"
"Total runtime: 2521.091 ms"

Andrus.