From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Declarative partitioning - another take
Date: Tue, 29 Nov 2016 20:24:27 +0900
Message-ID: <3c9f55c0-6353-052d-cd49-52f573972c55@lab.ntt.co.jp> (raw)
In-Reply-To: <aa874eaf-cd3b-0f75-c647-6c0ea823781e@lab.ntt.co.jp>
References: <ad16e2f5-fc7c-cc2d-333a-88d4aa446f96@lab.ntt.co.jp>
<CA+Tgmobs7C+4t5xm9LGopUb=2HF+SBE2EQ2NRH5EMd+drs6OUQ@mail.gmail.com>
<7aa6a14d-9476-afd7-2c09-6a016eb8c12b@lab.ntt.co.jp>
<CA+TgmoaPxXJ14eDVia514UiuQAXyZGqfbz8Qg3G4a8Rz2gKF7w@mail.gmail.com>
<377bc541-2108-4500-4b87-1b5936553555@lab.ntt.co.jp>
<CA+TgmoYJcUTcN7vVgg54GHtffH11JJWYZnfF4KiRxjV-iaACQg@mail.gmail.com>
<9ac4ba01-35cf-658b-5a57-c516c4ccedff@lab.ntt.co.jp>
<CA+TgmoY=tGtVh-nLiaZGT7cTNGHdWbpe+2JRsDLmG_sGbC4QZg@mail.gmail.com>
<795e4902-b70c-a18c-7e88-c40effee0f06@lab.ntt.co.jp>
<CA+Tgmob1bTucKsHcV_bfd6ym-CCYDJUZDUQmT77bZjiqPL8Saw@mail.gmail.com>
<132d3214-e845-4b16-d734-4e768537bc4a@lab.ntt.co.jp>
<CA+TgmoYdoRAUkiv0nJV8y63rYLNVzb60mQBzKZQq9RDMmPD1QQ@mail.gmail.com>
<8d7c35e3-1c85-33d0-4440-0a75bf9d31cd@lab.ntt.co.jp>
<56ec5504-c4d7-534c-8832-696dddfd58d1@lab.ntt.co.jp>
<CA+Tgmoarz2hkxoNddx9Ng_HMeoq4G8Q-R-teQtwmE8nCxkAJow@mail.gmail.com>
<aa874eaf-cd3b-0f75-c647-6c0ea823781e@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2016/11/17 20:27, Amit Langote wrote:
> On 2016/11/16 4:21, Robert Haas wrote:
>> Have you done any performance testing on the tuple routing code?
>> Suppose we insert a million (or 10 million) tuples into an
>> unpartitioned table, a table with 10 partitions, a table with 100
>> partitions, a table with 1000 partitions, and a table that is
>> partitioned into 10 partitions each of which has 10 subpartitions.
>> Ideally, the partitioned cases would run almost as fast as the
>> unpartitioned case, but probably there will be some overhead.
>> However, it would be useful to know how much. Also, it would be
>> useful to set up the same cases with inheritance using a PL/pgsql ON
>> INSERT trigger for tuple routing and compare. Hopefully the tuple
>> routing code is far faster than a trigger, but we should make sure
>> that's the case and look for optimizations if not. Also, it would be
>> useful to know how much slower the tuple-mapping-required case is than
>> the no-tuple-mapping-required case.
>
> OK, I will share the performance results soon.
Sorry about the delay; here are some numbers with the following
partitioning schema:
# plain table
create table plain (a date, b int, c int);
# partitioned table
create table ptab (a date, b int, c int) partition by range (a, b);
Partitions (not the full commands):
ptab_00001 for values from ('2016-11-29', 1) to ('2016-11-29', 1000);
ptab_00002 for values from ('2016-11-29', 1000) to ('2016-11-29', 2000);
...
ptab_00005 for values from ('2016-11-29', 4000) to ('2016-11-29', 5000);
ptab_00006 for values from ('2016-11-30', 1) to ('2016-11-30', 1000);
...
...
ptab_NNNNN for values from ('20XX-XX-XX', 4000) to ('20XX-XX-XX', 5000);
# inheritance partitioned table
create table itab (a date, b int, c int);
create table itab_00001 (
check part_check check (a = '2016-11-29' and b >= 1 and b < 1000)
) inherits (itab);
...
create table itab_00005 (
check part_check check (a = '2016-11-29' and b >= 4000 and b < 5000)
) inherits (itab);
create table itab_0006 (
check part_check check (a = '2016-11-30' and b >= 1and b < 1000)
) inherits (itab);
...
...
create table itab_NNNNN (
check part_check check (a = '2016-11-29' and b >= 4000 and b < 5000)
) inherits (itab);
The BR trigger (on itab) procedure as follows:
CREATE OR REPLACE FUNCTION itab_ins_trig()
RETURNS TRIGGER AS $$
DECLARE
partno text;
BEGIN
SELECT to_char((NEW.a - '2016-11-29'::date) * 5 + NEW.b / 1000 + 1,
'fm00000') INTO partno;
EXECUTE 'INSERT INTO itab_' || partno || ' SELECT $1.*' USING NEW;
RETURN NULL;
END; $$ LANGUAGE plpgsql;
Note that the tuple-routing procedure above assumes a fixed-stride range
partitioning scheme (shown as tg-direct-map below). In other cases, the
simplest approach involves defining a if-else ladder, which I tried too
(shown as tg-if-else below), but reporting times only for up to 200
partitions at most (I'm sure there might be ways to be smarter there
somehow, but I didn't; the point here may only be to compare the new
tuple-routing code's overhead vs. trigger overhead in the traditional method).
# All times in seconds (on my modestly-powerful development VM)
#
# nrows = 10,000,000 generated using:
#
# INSERT INTO $tab
# SELECT '$last'::date - ((s.id % $maxsecs + 1)::bigint || 's')::interval,
# (random() * 5000)::int % 4999 + 1,
# case s.id % 10
# when 0 then 'a'
# when 1 then 'b'
# when 2 then 'c'
# ...
# when 9 then 'j'
# end
# FROM generate_series(1, $nrows) s(id)
# ORDER BY random();
#
# The first item in the select list is basically a date that won't fall
# outside the defined partitions.
Time for a plain table = 98.1 sec
#part parted tg-direct-map tg-if-else
===== ====== ============= ==========
10 114.3 1483.3 742.4
50 112.5 1476.6 2016.8
100 117.1 1498.4 5386.1
500 125.3 1475.5 --
1000 129.9 1474.4 --
5000 137.5 1491.4 --
10000 154.7 1480.9 --
Then for a 2-level partitioned table with each of the above partitions
partitioned by list (c), with 10 sub-partitions each as follows:
ptab_NNNNN_a for values in ('a');
ptab_NNNNN_b for values in ('b');
...
ptab_NNNNN_k for values in ('j');
I didn't include the times for inheritance table with a routing trigger in
this case, as it seems that the results would look something like the above:
Time for a plain table = 98.1 sec
#part (sub-)parted
===== ============
10 127.0
50 152.3
100 156.6
500 191.8
1000 187.3
Regarding tuple-mapping-required vs no-tuple-mapping-required, all cases
currently require tuple-mapping, because the decision is based on the
result of comparing parent and partition TupleDesc using
equalTupleDescs(), which fails so quickly because TupleDesc.tdtypeid are
not the same. Anyway, I simply commented out the tuple-mapping statement
in ExecInsert() to observe just slightly improved numbers as follows
(comparing with numbers in the table just above):
#part (sub-)parted
===== =================
10 113.9 (vs. 127.0)
100 135.7 (vs. 156.6)
500 182.1 (vs. 191.8)
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, robertmhaas@gmail.com, amitlangote09@gmail.com, rajkumar.raghuwanshi@enterprisedb.com, ashutosh.bapat@enterprisedb.com
Subject: Re: Declarative partitioning - another take
In-Reply-To: <3c9f55c0-6353-052d-cd49-52f573972c55@lab.ntt.co.jp>
* 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