On Tue, Nov 29, 2016 at 6:24 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > # 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 --
Very nice! Obviously, it would be nice if the overhead were even lower, but it's clearly a vast improvement over what we have today. > 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) I think you should definitely try to get that additional speedup when you can. It doesn't seem like a lot when you think of how much is already being saved, but a healthy number of users are going to compare it to the performance on an unpartitioned table rather than to our historical performance. 127/98.1 = 1.29, but 113.9/98.1 = 1.16 -- and obviously a 16% overhead from partitioning is way better than a 29% overhead, even if the old overhead was a million percent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
