From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to speed up query |
Date: | 2007-06-11 12:23:48 |
Message-ID: | f4jevm$da1$2@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Ah! 3.) should read:
> CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
> DELETE FROM firma1.dok WHERE dokumnr NOT IN (SELECT dukumnr FROM
> mydel);
I need to delete from firma1.rid table
So I cannot use this suggestion since firma1.dok.dokumnr is already unique
(primary key).
> Or 4.)
> If "NOT IN" should be the culprit, there is an alternative:
> ( I seem to remember issues with its performance in the past, but
> hasn't that been improved? Not sure.)
> Haven't tested, whether the temp table is useful here:
>
> CREATE TEMP TABLE mydel AS
> SELECT d.dokumnr
> FROM firma1.dok d
> LEFT JOIN (SELECT DISTINCT dokumnr FROM firma1.rid) r USING (dokumnr)
> WHERE r.dokumnr IS NULL;
> DELETE FROM firma1.dok USING mydel WHERE firma1.dok.doumnr =
> mydel.documnr;
I tried
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;
and this runs 1 seconds intead for 2.2 hours.
Thank you very much.
This works!
It's sad that PostgreSQL cannot optimize this delete statement
automatically.
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeremy Nix | 2007-06-11 12:29:53 | Re: Functions that return both Output Parameters and recordsets |
Previous Message | Mike Gould | 2007-06-11 12:19:33 | Distributing PostGres database to various customers |