pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andreas Pflug <pgadmin@pse-consulting.de>
To: pgsql-performance@postgresql.org
Subject: JOIN with inherited table ignores indexes
Date: Thu, 08 Jun 2006 13:40:33 +0200
Message-ID: <44880CB1.90307@pse-consulting.de> (raw )
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
view thread (4+ messages) latest in thread
Message-ID: <44880CB1.90307@pse-consulting.de>
Permalink: ../44880CB1.90307@pse-consulting.de/
Also on: postgresql.org/message-id/44880CB1.90307@pse-consulting.de
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgadmin@pse-consulting.de
Subject: Re: JOIN with inherited table ignores indexes
In-Reply-To: <44880CB1.90307@pse-consulting.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox