pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
ODP: Planner performance in partitions 2+ messages / 1 participants [nested] [flat]
* ODP: Planner performance in partitions @ 2019-08-12 20:03 Piotr Włodarczyk <piotrwlodarczyk89@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Piotr Włodarczyk @ 2019-08-12 20:03 UTC (permalink / raw) To: MichaelDBA <MichaelDBA@sqlexec.com>; Michael Lewis <mlewis@entrata.com>; +Cc: Piotr Włodarczyk <piotr.wlodarczyk@gnb.pl>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org> @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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* ODP: Planner performance in partitions @ 2019-08-13 06:29 Piotr Włodarczyk <piotrwlodarczyk89@gmail.com> parent: Piotr Włodarczyk <piotrwlodarczyk89@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Piotr Włodarczyk @ 2019-08-13 06:29 UTC (permalink / raw) To: Michael Lewis <mlewis@entrata.com>; David Rowley <david.rowley@2ndquadrant.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Piotr Włodarczyk <piotr.wlodarczyk@gnb.pl>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org> As you wrote we have about 400/500 partitions in real life. So time problem is much smaller, but still it is and in one place of aur application we have decided to help DB and we're indicating in query exact partition we need. What pushed me to do this test? Just curiosity I think. After I saw in pg_locks that all partitions which was selected in uncommitted transaction have ACCESS SHARED i've started thinking about efficiency. And that way here we are. Why we need some hundred partitions? It’s because our main table (public.book) have hundreds of millions records. It’s not maintainable. VACUUM never ends, space on device is huge and we cannot take database down for longer that 2-3 hours, what is too short to maintain them manually. So we've partitioned them on two levels. First on id_owner (which is in every query) and the second level based on date. It’ll help as detach partitions with old data we no longer need. Pozdrawiam, Piotr Włodarczyk Od: Michael Lewis Wysłano: wtorek, 13 sierpnia 2019 00:37 Do: David Rowley DW: Piotr Włodarczyk; MichaelDBA; Piotr Włodarczyk; pgsql-performance@lists.postgresql.org Temat: Re: Planner performance in partitions Was there a reason to exceed 100-500 partitions in real life that pushed you to do this test? Is there some issue you see when using 100 partitions that is solved or reduced in severity by increasing to 1200 or 6000 partitions? ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-08-13 06:29 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-08-12 20:03 ODP: Planner performance in partitions Piotr Włodarczyk <piotrwlodarczyk89@gmail.com> 2019-08-13 06:29 ` Piotr Włodarczyk <piotrwlodarczyk89@gmail.com>
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