Thread: 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

On Sun, Feb 23, 2020 at 04:12:09AM -0600, Justin Pryzby wrote:
> How large are the partitions and how many indexes each, and how large are they?
> Each partition will be stat()ed ... for every query.

I should have said that's every 1GB "segment" is stat()ed for every query.

> This was resolved in pg12:
> https://commitfest.postgresql.org/21/1778/

+ https://www.postgresql.org/about/featurematrix/detail/320/

-- 
Justin

On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wrote:
>    - Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180
Partitions.Howeverwe have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get
comparableperformance.   
 

I didn't hear how large the tables and indexes are.

>    - We need to look current partition and previous partition for all of our use-cases/queries.

Do you mean that a given query is only going to hit 2 partitions ?  Or do you
mean that all but the most recent 2 partitions are "archival" and won't be
needed by future queries ?

> Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc.
Alsolet me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
 

You should determine what an acceptable planning speed is, or the best balance
of planning/execution time.  Try to detach half your current partitions and, if
that gives acceptable performance, then partition by day/2 or more.  You could
make a graph of (planning and total) time vs npartitions, since I think it's
likely to be nonlinear.

I believe others have reported improved performance under v11 with larger
numbers of partitions, by using "partitions of partitions".  So you could try
making partitions by month themselves partitioned by day.

>    - Our use case is limited to simple selects (we don't join with the other
>    tables) however, we are expecting ~70 million records inserted per day
>    and there would be couple of updates on each records where average record
>    size would be ~ 1.5 KB.

>  shared_buffers                         | 1048576

If you care about INSERT performance, you probably need to make at least a
single partition's index fit within shared_buffers (or set shared_buffers such
that it fits).  Use transactions around your inserts.  If your speed is not
limited by I/O, you could further use multiple VALUES(),() inserts, or maybe
prepared statements.  Maybe synchronous_commit=off.

If you care about (consistent) SELECT performance, you should consider
VACUUMing the tables after bulk inserts, to set hint bits (and since
non-updated tuples won't be hit by autovacuum).  Or maybe VACUUM FREEZE to
freeze tuples (since it sounds like a typical page is unlikely to ever be
updated).

-- 
Justin