Date: Thu, 8 Jun 2006 10:22:15 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Andreas Pflug <pgadmin@pse-consulting.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: JOIN with inherited table ignores indexes
Message-ID: <20060608152215.GM45331@pervasive.com>
References: <44880CB1.90307@pse-consulting.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <44880CB1.90307@pse-consulting.de>
User-Agent: Mutt/1.5.11

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