Horton

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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>

View thread View on postgr.es
From: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
To: Rafia Sabih <rafia.sabih@enterprisedb.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-07-21T06:24:40Z
Lists: pgsql-hackers 
On Fri, Jul 21, 2017 at 11:42 AM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:
>
> Following the discussion at [1], with the patch Thomas posted there, now Q21
> completes in some 160 seconds.

Your earlier reports mentioned unpartitioned case taking 300 seconds,
partitioned case without partition-wise join taking 600 seconds and
with partition-wise join it taking 3200 seconds. My experiements
showed that those have changed to 70s, 160s and 160s resp. This is
with Thomas's patch. Can you please confirm?

> The plan is changed for the good but does not
> use partition-wise join.

As explained earlier, this is because the tables are not partitioned
similarly. Please try with lineitem and orders partitioned similarly
i.e. same number of partitions and exactly same ranges.


> Not just the join orders but the join strategy itself changed, with the
> patch no hash semi join is picked which was consuming most time there,
> rather nested loop semi join is in picture now, though the estimates are
> still way-off, but the change in join-order made them terrible from
> horrible. It appears like this query is performing efficient now
> particularly because of worse under-estimated hash-join as compared to
> under-estimated nested loop join.

Earlier it was using partition-wise join between lineitems (l1, l2,
l3) since it's the same table. Now for some reason the planner doesn't
find joining them to each other a better strategy, instead they are
joined indirectly so we don't see partition-wise join being picked. We
should experiment with orders and lineitems being partitioned
similarly. Can you please provide that result?

>
> For the hash-semi-join:
> ->  Hash  (cost=3449457.34..3449457.34 rows=119994934 width=8) (actual
> time=180858.448..180858.448 rows=119994608 loops=3)
>                                                    Buckets: 33554432
> Batches: 8  Memory Usage: 847911kB
>
> Overall, this doesn't look like a problem of partition-wise join patch
> itself.
>

Thanks for confirming it.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Commits

  1. Basic partition-wise join functionality.

  2. Assorted preparatory refactoring for partition-wise join.

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

  4. Stamp 10beta2.

  5. Eat XIDs more efficiently in recovery TAP test.

  6. Abstract logic to allow for multiple kinds of child rels.

  7. Implement SortSupport for macaddr data type

  8. Attempt to stabilize grouping sets regression test plans.

  9. Teach xlogreader to follow timeline switches

  10. Don't scan partitioned tables.

  11. Fix grammar.

  12. postgres_fdw: Push down FULL JOINs with restriction clauses.

  13. Some preliminary refactoring towards partitionwise join.

  14. contrib/amcheck needs RecentGlobalXmin to be PGDLLIMPORT'ified.

  15. Print test parameters like "foo: 123", and results like "foo = 123".