From: | "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | Justin Pryzby <pryzby(at)telsasoft(dot)com>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: How to make partitioning scale better for larger numbers of partitions |
Date: | 2018-07-17 03:01:49 |
Message-ID: | 25C1C6B2E7BE044889E4FE8643A58BA963AAA7C7@G01JPEXMBKW03 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2018/07/17 10:49, Amit Langote wrote:
>Perhaps, Kato-san only intended to report that the time that planner spends for a partitioned table with 1100 partitions is just too high compared to the time it spends on a non-partitioned table.
yes, It is included for the purposes of this comparison.
The purpose of this comparison is to find where the partitioning bottleneck is.
Using the bottleneck as a hint of improvement, I'd like to bring the performance of partitioned table closer to the performance of unpartitioned table as much as possible.
-----Original Message-----
From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp]
Sent: Tuesday, July 17, 2018 10:49 AM
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>; Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>; PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: How to make partitioning scale better for larger numbers of partitions
On 2018/07/13 22:10, Ashutosh Bapat wrote:
> On Fri, Jul 13, 2018 at 9:23 AM, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
>>> I wondered if you compared to PG10 or to inheritence-partitioning (parent with relkind='r' and either trigger or rule or >INSERT/UPDATE directly into child) ?
>>
>> Thank you for your reply.
>>
>> I compared to PG11beta2 with non-partitioned table.
>>
>> Non-partitioned table has 1100 records in one table.
>> Partitioned table has one record on each leaf partitions.
>>
>
> I don't think partitioning should be employed this way even for the
> sake of comparison. Depending upon the size of each tuple, 1100 tuples
> are inserted into a single table, they will probably occupy few
> hundred pages. In a partitioned table with one tuple per partition
> they will occupy 1100 pages at least. There is other space, locking
> overheads to maintain 1100 tables. I think the right way to compare is
> to have really large that which really requires 1100 partitions and
> then compare performance by putting that data in 1100 partitions and
> in an unpartitioned table. Even with that kind of data, you will see
> some difference in performance, but that won't be as dramatic as you
> report.
>
> I might be missing something though.
Perhaps, Kato-san only intended to report that the time that planner spends for a partitioned table with 1100 partitions is just too high compared to the time it spends on a non-partitioned table. It is and has been clear that that's because the planning time explodes as the number of partitions increases.
If there's lots of data in it, then the result will look completely different as you say, because scanning a single partition (of the 1100
total) will spend less time than scanning a non-partitioned table containing 1100 partitions worth of data. But the planning time would still be more for the partitioned table, which seems to be the point of this benchmark.
Thanks,
Amit
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2018-07-17 03:14:11 | Re: How to make partitioning scale better for larger numbers of partitions |
Previous Message | David Fetter | 2018-07-17 01:49:22 | Re: Make foo=null a warning by default. |