To: "Julian Mehnle" <julian@mehnle.net>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Bad query plans for queries on partitioned table
In-Reply-To: <200712041944.13543.julian@mehnle.net> (Julian Mehnle's message
	of "Tue\, 4 Dec 2007 19\:44\:08 +0000")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <200712041944.13543.julian@mehnle.net>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Tue, 04 Dec 2007 20:15:21 +0000
Message-ID: <87ir3e6wti.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Julian Mehnle" <julian@mehnle.net> writes:

> However, if I restrict the query to just the partitions that actually do
> have data in them ...

There are a few things going on here. 

1) The optimizer can't build a plan which ignores those partitions because the
statistics are just approximations. You could insert into one of them at any
time and the statistics won't update immediately. If you have a partition
which is empty of some type of data you can put a constraint on it to promise
the optimizer that that condition will stay true.

2) The optimizer is assuming that empty tables have a default 1,000 records in
them with no idea about their statistics. Otherwise you get terrible plans on
tables which have just been created or never analyzed. In this case that's
causing it to think there will be tons of matches on what is apparently a very
selective criterion.

3) The optimizer is a bit dumb about partitioned tables. But I'm not sure if
that's actually the fault here.

Try adding one record of data to each of those partitions or putting a
constraint on them which will allow constraint_exclusion (I assume you have
that enabled?) to kick in. You'll still be bitten by the parent table but
hopefully that's not enough to cause a problem.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning