From: Gregory Stark <stark@enterprisedb.com>
To: Julian Mehnle <julian@mehnle.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plans for queries on partitioned table
Date: Tue, 04 Dec 2007 20:15:21 +0000
Message-ID: <87ir3e6wti.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <200712041944.13543.julian@mehnle.net>
References: <200712041944.13543.julian@mehnle.net>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, julian@mehnle.net
Subject: Re: Bad query plans for queries on partitioned table
In-Reply-To: <87ir3e6wti.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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