Hi,
I have a two tables: master and detail.
I search all master rows without a detail row.
master: 10000 rows
detail: 29800 rows
Although all three solution have the same result, The execution time
differs very much.
My naive first solution was quite slow. Why is it so slow?
I guess (select d.master_id from detail as d) gets executed for every
master-row. But why? Shouldn't
it be possible to calculate it once and then reuse it?
Has someone a better statement?
############################################ NOT IN
SELECT "master"."id" FROM "master" WHERE master.id not in (select
d.master_id from detail as d);
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on master (cost=782.68..2661482.52 rows=5132 width=16)
(actual time=2520.509..340387.326 rows=43 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=782.68..1226.57 rows=29789 width=4) (actual
time=0.005..16.696 rows=9482 loops=10269)
-> Seq Scan on detail d (cost=0.00..606.89 rows=29789
width=4) (actual time=0.009..52.536 rows=29793 loops=1)
Total runtime: 340387.898 ms
(6 Zeilen)
############################################ JOIN
SELECT "master"."id" FROM "master" LEFT OUTER JOIN "detail" ON
("master"."id" = "detail"."master_id") WHERE "detail"."id" IS NULL
;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=0.00..3061.08 rows=14894 width=16) (actual
time=107.521..153.840 rows=43 loops=1)
Merge Cond: (master.id = detail.master_id)
Filter: (detail.id IS NULL)
-> Index Scan using master_pkey on master (cost=0.00..486.50
rows=10265 width=16) (actual time=0.024..20.519 rows=10269 loops=1)
-> Index Scan using detail_master_id on detail (cost=0.00..2176.55
rows=29789 width=8) (actual time=0.014..59.256 rows=29793 loops=1)
Total runtime: 153.974 ms
(6 Zeilen)
############################################ NOT EXISTS
SELECT "master"."id" FROM master WHERE NOT EXISTS (SELECT 1 FROM detail
AS d WHERE d.master_id=master.id);
Seq Scan on master (cost=0.00..27278.09 rows=5132 width=16) (actual
time=0.327..61.911 rows=43 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using detail_master_id on detail d
(cost=0.00..50.16 rows=19 width=0) (actual time=0.004..0.004 rows=1
loops=10269)
Index Cond: (master_id = $0)
Total runtime: 62.028 ms
(6 Zeilen)
--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
