Re: JOIN with inherited table ignores indexes

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461