From: Justin Pryzby <pryzby@telsasoft.com>
To: Ravi Garg <ravi.garg@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table
Date: Sun, 23 Feb 2020 04:12:09 -0600
Message-ID: <20200223101209.GU31889@telsasoft.com> (raw)
In-Reply-To: <1789489940.5008704.1582451790367@mail.yahoo.com>
References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com>
<1789489940.5008704.1582451790367@mail.yahoo.com>
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 performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning 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 is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes 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
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: pryzby@telsasoft.com, ravi.garg@yahoo.com, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table
In-Reply-To: <20200223101209.GU31889@telsasoft.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