From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to speed up query |
Date: | 2007-06-13 13:13:15 |
Message-ID: | f4oqhc$29ta$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-06-13 13:36:43 | Re: pg_xlog - files are guaranteed to be sequentialy named? |
Previous Message | Albe Laurenz | 2007-06-13 13:09:56 | Re: setof or array as input parameter to postgresql 8.2 functions |