Re: Strange explain on partitioned tables

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us