Date: Sun, 23 Feb 2020 04:12:09 -0600
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
Message-ID: <20200223101209.GU31889@telsasoft.com>
References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com>
 <1789489940.5008704.1582451790367@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1789489940.5008704.1582451790367@mail.yahoo.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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