Message-ID: <4C4A763C.8090701@2ndquadrant.com>
Date: Sat, 24 Jul 2010 01:12:28 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: gvfontenay@lbl.gov
CC: pgsql-performance@postgresql.org
Subject: Re: Strange explain on partitioned tables
References: <4C47F8C8.10005@edd.fr> <4C484874.3060609@2ndquadrant.com>
	<4C4A11CB.6040800@lbl.gov>
In-Reply-To: <4C4A11CB.6040800@lbl.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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