From: Piotr Włodarczyk <piotrwlodarczyk89@gmail.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Piotr Włodarczyk <piotr.wlodarczyk@gnb.pl>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: ODP: Planner performance in partitions
Date: Mon, 12 Aug 2019 22:03:48 +0200
Message-ID: <5d51c622.1c69fb81.4032d.34d9@mx.google.com> (raw)
In-Reply-To: <458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com>
References: <5675f9f771f04d9f8b5cffe16f4a4175@EX13MBX02.gb.ad.corp>
<CAHOFxGrPiuFFNETAbV3rcw1COns8vJbgPm5zrGiSeO9GZPRshQ@mail.gmail.com>
<458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com>
@Michael Lewis: I know documentation. I'm just considerations about possible performance tricks in current production version. I've tested this on V12 on another computer and I can say that I'm impressed. I've checked on 1200 partitions and times are:
PostgreSQL11.5:
• select on main partition (public.book): planner: 60ms, execution: 5ms
• select on partitioned table (public.book_1-1000): planner: 2.7 ms, execution: 2,4 ms
PostgreSQL 12B3:
• select on main partition (public.book): planner: 2,5ms , execution: 1,2ms
• select on partitioned table (public.book_1-1000): planner: 2.5 ms, execution: 1,2 ms
So looking at above results we have two options:
• Wait for 12.0 stable version 😉
• Wait for patches to 11 – PostgreSQL Team: can You do this? 😊
Pozdrawiam,
Piotr Włodarczyk
Od: MichaelDBA
Wysłano: poniedziałek, 12 sierpnia 2019 21:25
Do: Michael Lewis
DW: Piotr Włodarczyk; pgsql-performance@lists.postgresql.org
Temat: Re: Planner performance in partitions
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
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: piotrwlodarczyk89@gmail.com, MichaelDBA@sqlexec.com, mlewis@entrata.com, piotr.wlodarczyk@gnb.pl, pgsql-performance@lists.postgresql.org
Subject: Re: ODP: Planner performance in partitions
In-Reply-To: <5d51c622.1c69fb81.4032d.34d9@mx.google.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