From: Greg Smith <greg@2ndquadrant.com>
To: gvfontenay@lbl.gov
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange explain on partitioned tables
Date: Sat, 24 Jul 2010 01:12:28 -0400
Message-ID: <4C4A763C.8090701@2ndquadrant.com> (raw)
In-Reply-To: <4C4A11CB.6040800@lbl.gov>
References: <4C47F8C8.10005@edd.fr>
<4C484874.3060609@2ndquadrant.com>
<4C4A11CB.6040800@lbl.gov>
Gerald Fontenay wrote:
>
>> The PostgreSQL partitioning system is aimed to support perhaps a
>> hundred inherited tables. You can expect to get poor performance on
>> queries if you create 1000 of them.
>
> Why is that you would expect poor performance for say 1000 or more?
When the query planner executes, it has to scan through every child
table to run the constraint exclusion algorithm for determining whether
that table needs to be included in the query results or not. The time
that takes is proportional to the number of partitions. If your queries
take a long time to execute relative to how long they take to plan, you
may not have noticed this. But for shorter queries, and ones where
there are lots of joins that require many plans be evaluated, the
planning overhead increase can be significant. The threshold for where
it becomes painful is obviously workload dependent, but the thing to be
careful of is that supporting very large numbers of partitions is not
something that the database query planner has been optimized for yet.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, gvfontenay@lbl.gov
Subject: Re: Strange explain on partitioned tables
In-Reply-To: <4C4A763C.8090701@2ndquadrant.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