From: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | Piotr Włodarczyk <piotr(dot)wlodarczyk(at)gnb(dot)pl>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Planner performance in partitions |
Date: | 2019-08-12 19:24:58 |
Message-ID: | 458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Piotr Włodarczyk | 2019-08-12 20:03:48 | ODP: Planner performance in partitions |
Previous Message | Michael Lewis | 2019-08-12 19:05:25 | Re: Planner performance in partitions |