Re: how to speed up query

On Jun 9, 12:15 am, Erwin Brandstetter <brsaw(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> 3.) Write results of the subquery in a temp table, then DELETE:
>
> CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
> DELETE FROM firma1.dok USING mydel WHERE firma1.rid.doumnr =
> mydel.doumnr;

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);

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;

Regards
Erwin