Subject: Re: Planner performance in partitions
To: Michael Lewis <mlewis@entrata.com>
Cc: =?UTF-8?Q?Piotr_W=c5=82odarczyk?= <piotr.wlodarczyk@gnb.pl>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
References: <5675f9f771f04d9f8b5cffe16f4a4175@EX13MBX02.gb.ad.corp>
 <CAHOFxGrPiuFFNETAbV3rcw1COns8vJbgPm5zrGiSeO9GZPRshQ@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com>
Date: Mon, 12 Aug 2019 15:24:58 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/6.1.18
MIME-Version: 1.0
In-Reply-To: 
 <CAHOFxGrPiuFFNETAbV3rcw1COns8vJbgPm5zrGiSeO9GZPRshQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------D2DD700252F0E55F5787EA27"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------D2DD700252F0E55F5787EA27
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Queries against tables with a lot of partitions (> 1000) start to incur 
an increasing planning time duration even with the current version, 
V11.  V12 purportedly has fixed this problem, allowing thousands of 
partitioned tables without a heavy planning cost.  Can't seem to find 
the threads on this topic, but there are out there.  I personally noted 
a gigantic increase in planning time once I got past 1500 partitioned 
tables in V11.

On another note, hopefully they have fixed runtime partition pruning in 
V12 since V11 introduced it but some query plans don't use it, so you 
have to reconstruct some queries to sub queries to make it work correctly.

Regards,
Michael Vitale


Michael Lewis wrote on 8/12/2019 3:05 PM:
> "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." (emphasis added)
>
> --https://www.postgresql.org/docs/current/ddl-partitioning.html


--------------D2DD700252F0E55F5787EA27
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body bgcolor="#FFFFFF" text="#000000">Queries against tables 
with a lot of partitions (&gt; 1000) start to incur an increasing 
planning time duration even with the current version, V11.  V12 
purportedly has fixed this problem, allowing thousands of partitioned 
tables without a heavy planning cost.  Can't seem to find the threads on
 this topic, but there are out there.  I personally noted a gigantic 
increase in planning time once I got past 1500 partitioned tables in 
V11.<br>
<br>
On another note, hopefully they have fixed runtime partition pruning in 
V12 since V11 introduced it but some query plans don't use it, so you 
have to reconstruct some queries to sub queries to make it work 
correctly.<br>
<br>
Regards,<br>
Michael Vitale<br>
<br>
<br>
<span>Michael Lewis wrote on 8/12/2019 3:05 PM:</span><br>
<blockquote type="cite" 
cite="mid:CAHOFxGrPiuFFNETAbV3rcw1COns8vJbgPm5zrGiSeO9GZPRshQ@mail.gmail.com">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <div dir="ltr"><div>"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 <b><i>up to a few 
hundred partitions fairly well</i></b>, 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." (emphasis added)</div><div><br><div>--<a 
href="https://www.postgresql.org/docs/current/ddl-partitioning.html" 
moz-do-not-send="true">https://www.postgresql.org/docs/current/ddl-partitioning.html</a></div></div></div>
</blockquote>
<br>
</body></html>

--------------D2DD700252F0E55F5787EA27--