Re: how to speed up query

>> 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;
>
> As I mentioned when I proposed it, the temp table may not even be
> necessary. The important part is the LEFT JOIN instead of the NOT IN
> (as Martijn has explained).
> You could try the direct approach ...
>
> DELETE FROM rid
> USING ( SELECT r.dokumnr
> FROM rid r
> LEFT JOIN dok d USING (dokumnr)
> WHERE d.dokumnr IS NULL) x
> WHERE rid.dokumnr = x.dokumnr;
> ... and see which runs faster. Probably it does not make much of a
> difference.

Thank you.
I changed my DELETE commands to use internal table. This works fast.

I tried to change my update commands also to use internal table.
However, this causes update command to run 310 minutes:

update bilkaib SET cr4objekt=NULL
FROM ( SELECT r.cr4objekt as key
FROM bilkaib r
LEFT JOIN yksus4 d ON d.YKSUS =r.cr4objekt
WHERE d.YKSUS IS NULL) mydel
WHERE cr4objekt IS NOT NULL AND
bilkaib.cr4objekt= mydel.key;

No idea why this does not work fast like in DELETE command. cr4objekt type
is CHAR(10) maybe this makes internal table slow.

So I changed my script to

DROP TABLE if exists mydel;

CREATE TEMP TABLE mydel AS
SELECT r.<<cchildkey>> as key
FROM <<m.cChildtable>> r
LEFT JOIN <<cmaintable>> d ON d.<<mainkey>>=r.<<cchildkey>>
WHERE d.<<mainkey>> IS NULL;

update <<m.cChildtable>> SET <<cchildkey>>=NULL
FROM mydel
WHERE <<cchildkey>> IS NOT NULL AND
<<m.cChildtable>>.<<cchildkey>>= mydel.key;

Hope this will run fast (will test tomorrow).

My original skript

UPDATE <<m.cChildtable>> SET <<cchildkey>>=NULL
WHERE <<cchildkey>> IS NOT NULL AND
<<cchildkey>> NOT IN (SELECT <<mainkey>> FROM <<cmaintable>>);

runs 27 minutes in some cases.

> If the temp table works for you, you might be interested in a new
> feature of 8.2: CREATE TEMP TABLE AS ...ON COMMIT DROP;
> http://www.postgresql.org/docs/current/static/sql-createtableas.html

Per Tom remart , I removed transactions. Now every statement runs in
separate transaction.
In this case ON COMMIT DROP is useless.
ON COMMIT DROP exists in 8.1 also.
8.2 adds DROP IF EXISTS.

>> 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"
>
> If the indices are present (and visible) at the time of execution, as
> you described it, we should be seeing index scans on dok_dokumnr_idx
> and rid_dokumnr_idx instead of sequential scans.
>
> That's what I get on a similar query in one of my databases:
> EXPLAIN ANALYZE SELECT a.adr_id FROM cp.adr a LEFT JOIN cp.kontakt k
> USING (adr_id) WHERE k.adr_id IS NULL;
>
> Merge Left Join (cost=0.00..1356.31 rows=10261 width=4) (actual
> time=0.096..56.759 rows=3868 loops=1)
> Merge Cond: ("outer".adr_id = "inner".adr_id)
> Filter: ("inner".adr_id IS NULL)
> -> Index Scan using adr_pkey on adr a (cost=0.00..947.54
> rows=10261 width=4) (actual time=0.012..23.118 rows=10261 loops=1)
> -> Index Scan using kontakt_adr_id_idx on kontakt k
> (cost=0.00..295.47 rows=7011 width=4) (actual time=0.007..13.299
> rows=7011 loops=1)
> Total runtime: 58.510 ms

I have no idea why my query plan shows hash and your plan show merge.
My primary key (dokumnr is of type integer). Maybe this selects hash plan.

For my big database I got the following plan:

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

1 Hash Left Join (cost=7759.44..31738.44 rows=1
width=4) (actual time=112.572..761.121 rows=3
loops=1)
2 Hash Cond: (r.dokumnr = d.dokumnr)
3 Filter: (d.dokumnr IS NULL)
4 -> Seq Scan on rid r (cost=0.00..17424.64
rows=202464 width=4) (actual time=0.007..175.538
rows=202424 loops=1)
5 -> Hash (cost=6784.64..6784.64 rows=56064
width=4) (actual time=111.296..111.296 rows=56079
loops=1)
6 -> Seq Scan on dok d (cost=0.00..6784.64
rows=56064 width=4) (actual time=0.005..58.686
rows=56079 loops=1)
7 Total runtime: 761.311 ms

Since there are a lot of rows (202424 swown), this select command must use
indexes.
Without indexes it is not possible toobtain speed of 0.7 seconds.

Andrus.