Re: PostgreSQL 11 higher Planning time on Partitioned table

On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query
performancedifference between the un-partitioned and partitioned table I am getting huge difference in planning time.
Planningtime is very high on partitioned table.Similarly when I query by specifying partition name directly in query
theplanning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in
query,where planning time **6.231 ms** (Samples below).<br>
 

That's probably to be expected under pg11:

https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner
isgenerally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical
queriesallow the query planner to prune all but a small number of partitions. Planning times become longer and memory
consumptionbecomes higher as more partitions are added
 

> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on
x86_64-pc-linux-gnu,compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
 

How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query.  This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/

-- 
Justin