Re: JOIN with inherited table ignores indexes

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

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