RE: How to make partitioning scale better for larger numbers ofpartitions
От | Kato, Sho |
---|---|
Тема | RE: How to make partitioning scale better for larger numbers ofpartitions |
Дата | |
Msg-id | 25C1C6B2E7BE044889E4FE8643A58BA963AA84E7@G01JPEXMBKW03 обсуждение исходный текст |
Ответ на | Re: How to make partitioning scale better for larger numbers ofpartitions (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Ответы |
Re: How to make partitioning scale better for larger numbers ofpartitions
|
Список | pgsql-hackers |
Hi Amit, Thank you for letting me know challenges of partitioning. >So, while PG 11's overhead of partitioning during planning is less than PG 10's due to improved pruning algorithm, it'sstill pretty far from ideal, because it isn't just the pruning algorithm that had overheads. That makes sense. I also benchmark PG10. Actually, SELECT latency on PG11beta2 + patch1 is faster than PG10. SELECT latency with 800 leaf partition -------------------------------------- PG10 5.62 ms PG11 3.869 ms But, even PG11, SELECT statement takes 21.102ms on benchmark with 1600 leaf partitions. It takes a long time though partition pruning algorithm of PG11 is binary search. >The overheads I mention stem from the fact that for partitioning we still rely on the old planner code that's used to performinheritance planning, which requires to lock and open *all* partitions. I debug update statement execution on partitioned table. range_table_mutator seems process all leaf partitions. -----Original Message----- From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp] Sent: Friday, July 13, 2018 1:35 PM To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>; PostgreSQL mailing lists <pgsql-hackers@postgresql.org> Subject: Re: How to make partitioning scale better for larger numbers of partitions Kato-san, On 2018/07/13 11:58, Kato, Sho wrote: > Hi, > > I benchmarked on a RANGE partitioned table with 1.1k leaf partitions and no sub-partitioned tables. Thanks for sharing the results. > But, statement latencies on a partitioned table is much slower than on a non-partitioned table. > > UPDATE latency is 210 times slower than a non-partitioned table. > SELECT latency is 36 times slower than a non-partitioned table. > Surprisingly INSERT latency is almost same. Yes, INSERT comes out ahead because there is no overhead of partitioning in the planning phase. As David Rowley reportedon the nearby thread ("Speeding up INSERTs and UPDATEs to partitioned tables"), there is still significant overheadduring its execution, so we're still a bit a fair bit away from the best possible performance. For SELECT/UPDATE/DELETE, overhead of partitioning in the planning phase is pretty significant and gets worse as the numberof partitions grows. I had intended to fix that in PG 11, but we could only manage to get part of that work into PG11, with significant help from David and others. So, while PG 11's overhead of partitioning during planning is less thanPG 10's due to improved pruning algorithm, it's still pretty far from ideal, because it isn't just the pruning algorithmthat had overheads. In fact, PG 11 only removes the pruning overhead for SELECT, so UPDATE/DELETE still carry theoverhead that was in PG 10. The overheads I mention stem from the fact that for partitioning we still rely on the oldplanner code that's used to perform inheritance planning, which requires to lock and open *all* partitions. We have sofar been able to refactor just enough to use the new code for partition pruning, but there is much refactoring work leftto avoid needlessly locking and opening all partitions. Thanks, Amit
В списке pgsql-hackers по дате отправления: