Horton

Re: Partition-wise join for join between (declaratively) partitioned tables

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Rafia Sabih <rafia.sabih@enterprisedb.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Robert Haas <robertmhaas@gmail.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>, Thomas Munro <thomas.munro@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-09-19T10:20:05Z
Lists: pgsql-hackers 
Rafia Sabih wrote:

> On completing the benchmark for all queries for the above mentioned
> setup, following performance improvement can be seen,
> Query | Patch | Head
> 3  | 1455  |  1631
> 4  |  499  |  4344
> 5  |  1464  |  1606
> 10  |  1475  |  1599
> 12  |  1465  |  1790
> 
> Note that all values of execution time are in seconds.
> To summarise, apart from Q4, all other queries are showing somewhat
> 10-20% improvement.

Saving 90% of time on the slowest query looks like a worthy improvement
on its own right.  However, you're reporting execution time only, right?
What happens to planning time?  In a quick look,

$ grep 'Planning time' pg_part_*/4*
pg_part_head/4_1.out: Planning time: 3390.699 ms
pg_part_head/4_2.out: Planning time: 194.211 ms
pg_part_head/4_3.out: Planning time: 210.964 ms
pg_part_head/4_4.out: Planning time: 4150.647 ms
pg_part_patch/4_1.out: Planning time: 7577.247 ms
pg_part_patch/4_2.out: Planning time: 312.421 ms
pg_part_patch/4_3.out: Planning time: 304.697 ms
pg_part_patch/4_4.out: Planning time: 269.778 ms

I think the noise in these few results is too large to draw any
conclusions.  Maybe a few dozen runs of EXPLAIN (w/o ANALYZE) would tell
something significant?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Add test for partitionwise join involving default partition.

  2. Rewrite the code that applies scan/join targets to paths.

  3. Fix code related to partitioning schemes for dropped columns.

  4. Copy information from the relcache instead of pointing to it.

  5. Basic partition-wise join functionality.

  6. Associate partitioning information with each RelOptInfo.

  7. Expand partitioned table RTEs level by level, without flattening.

  8. Set partitioned_rels appropriately when UNION ALL is used.

  9. Remove dedicated B-tree root-split record types.

  10. Assorted preparatory refactoring for partition-wise join.

  11. Teach adjust_appendrel_attrs(_multilevel) to do multiple translations.

  12. Avoid unnecessary single-child Append nodes.

  13. Revisit handling of UNION ALL subqueries with non-Var output columns.