pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJOIN with inherited table ignores indexes
4+ messages / 3 participants
[nested] [flat]
* JOIN with inherited table ignores indexes
@ 2006-06-08 11:40 Andreas Pflug <pgadmin@pse-consulting.de>
2006-06-08 15:22 ` Re: JOIN with inherited table ignores indexes Jim C. Nasby <jnasby@pervasive.com>
2006-06-08 16:06 ` Re: JOIN with inherited table ignores indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Pflug @ 2006-06-08 11:40 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN with inherited table ignores indexes
2006-06-08 11:40 JOIN with inherited table ignores indexes Andreas Pflug <pgadmin@pse-consulting.de>
@ 2006-06-08 15:22 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-06-08 15:22 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: pgsql-performance
On Thu, Jun 08, 2006 at 01:40:33PM +0200, Andreas Pflug wrote:
> 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:
I'm pretty sure the issue is that the planner doesn't know what values
will be coming back from the subselect at plan time, so if the
distribution of values in attachment_nr isn't fairly constant you can g
et some pretty bad plans. Unfortunately, no one's figured out a good way
to fix this yet.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN with inherited table ignores indexes
2006-06-08 11:40 JOIN with inherited table ignores indexes Andreas Pflug <pgadmin@pse-consulting.de>
@ 2006-06-08 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-08 16:42 ` Re: JOIN with inherited table ignores indexes Andreas Pflug <pgadmin@pse-consulting.de>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-06-08 16:06 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: pgsql-performance
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Any explanation for this horror?
Existing releases aren't smart about planning joins to inheritance
trees. CVS HEAD is better...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JOIN with inherited table ignores indexes
2006-06-08 11:40 JOIN with inherited table ignores indexes Andreas Pflug <pgadmin@pse-consulting.de>
2006-06-08 16:06 ` Re: JOIN with inherited table ignores indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-08 16:42 ` Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 0 replies; 4+ messages in thread
From: Andreas Pflug @ 2006-06-08 16:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Any explanation for this horror?
>
>
> Existing releases aren't smart about planning joins to inheritance
> trees.
Using a view that UNIONs SELECT .. ONLY as replacement for the parent
table isn't any better. Is that improved too?
> CVS HEAD is better...
Customers like HEAD versions for production purposes :-)
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-08 16:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-08 11:40 JOIN with inherited table ignores indexes Andreas Pflug <pgadmin@pse-consulting.de>
2006-06-08 15:22 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-08 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-08 16:42 ` Andreas Pflug <pgadmin@pse-consulting.de>
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