I have this table setup on a 8.1.4 server:
pj_info_attach(attachment_nr, some more cols) -- index, 50k rows
pj_info_attach_compressable() INHERITS (pj_info_attach) -- index, 1M rows
pj_info_attach_not_compressable() INHERITS (pj_info_attach) -- index, 0
rows
EXPLAIN ANALYZE SELECT aes FROM pj_info_attach
WHERE attachment_nr in (.. 20 numeric key values.. )
yields a big bitmap index scan plan, 1.8ms total runtime, that's fine.
Using a subselect on zz_attachment_graustufentest, which has 20 rows of
exactly the key values entered manually in the query above:
EXPLAIN ANALYZE SELECT aes FROM pj_info_attach
WHERE attachment_nr in
(SELECT attachment_nr FROM zz_attachment_graustufentest)
gives 49s runtime, and full table scans.
Merge Join (cost=158472.98..164927.22 rows=107569 width=8)
(actual time=49714.702..49715.142 rows=20 loops=1)
Merge Cond: ("outer"."?column2?" = "inner"."?column3?")
-> Sort (cost=2.16..2.21 rows=20 width=13)(actual time=0.752..0.830
rows=20 loops=1)
Sort Key: (zz_attachment_graustufentest.attachment_nr)::numeric
-> Result (cost=1.63..1.73 rows=20 width=13) (actual
time=0.220..0.637 rows=20 loops=1)
-> Unique (cost=1.63..1.73 rows=20 width=13) (actual
time=0.210..0.459 rows=20 loops=1)
-> Sort (cost=1.63..1.68 rows=20 width=13) (actual
time=0.202..0.281 rows=20 loops=1)
Sort Key:
zz_attachment_graustufentest.attachment_nr
-> Seq Scan on zz_attachment_graustufentest
(cost=0.00..1.20 rows=20 width=13)
(actual time=0.007..0.092 rows=20 loops=1)
-> Sort (cost=158470.81..161160.04 rows=1075690 width=40)
(actual time=44705.196..47222.685 rows=589842 loops=1)
Sort Key: (public.pj_info_attach.attachment_nr)::numeric
-> Result (cost=0.00..32736.90 rows=1075690 width=40)
(actual time=0.023..21958.761 rows=1074930 loops=1)
-> Append (cost=0.00..32736.90 rows=1075690 width=40)
(actual time=0.015..13485.153
rows=1074930 loops=1)
-> Seq Scan on pj_info_attach (cost=0.00..1433.57
rows=49957 width=21)
(actual time=0.008..214.308 rows=49957 loops=1)
-> Seq Scan on pj_info_attach_compressable
pj_info_attach (cost=0.00..31285.73 rows=1024973 width=21)
(actual time=0.032..4812.090 rows=1024973 loops=1)
-> Seq Scan on pj_info_attach_not_compressable
pj_info_attach (cost=0.00..17.60 rows=760 width=40)
(actual time=0.005..0.005 rows=0 loops=1)
Total runtime: 49747.630 ms
Any explanation for this horror?
Regards,
Andreas
