Horton

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

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Rafia Sabih <rafia.sabih@enterprisedb.com>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.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-19T19:00:21Z
Lists: pgsql-hackers 
On Wed, Jul 19, 2017 at 12:24 AM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:
> On testing this patch for TPC-H (for scale factor 20) benchmark I found a
> regression for Q21, on head it was taking some 600 seconds and with this
> patch it is taking 3200 seconds. This comparison is on the same partitioned
> database, one using the partition wise join patch and other is without it.
> The execution time of Q21 on unpartitioned head is some 300 seconds. The
> explain analyse output for each of these cases is attached.

Interesting.

> This suggests that partitioning is not a suitable strategy for this query,
> but then may be partition wise should not be picked for such a case to
> aggravate the performance issue.

In the unpartitioned case, and in the partitioned case on head, the
join order is l1-(nation-supplier)-l2-orders-l3.  In the patched case,
the join order changes to l1-l2-supplier-orders-nation-l3.  If the
planner used the former join order, it wouldn't be able to do a
partition-wise join at all, so it must think that the l1-l2 join gets
much cheaper when done partitionwise, thus justifying a change in the
overall join order to be able to use partion-wise join.  But it
doesn't work out.

I think the problem is that the row count estimates for the child
joins seem to be totally bogus:

->  Hash Semi Join  (cost=309300.53..491665.60 rows=1 width=12)
(actual time=10484.422..15945.851 rows=1523493 loops=3)
  Hash Cond: (l1.l_orderkey = l2.l_orderkey)
  Join Filter: (l2.l_suppkey <> l1.l_suppkey)
  Rows Removed by Join Filter: 395116

That's clearly wrong.  In the un-partitioned plan, the join to l2
produces about as many rows of output as the number of rows that were
input (998433 vs. 962909); but here, a child join with a million rows
as input is estimated to produce only 1 row of output.  I bet the
problem is that the child-join's row count estimate isn't getting
initialized at all, but then something is clamping it to 1 row instead
of 0.

So this looks like a bug in Ashutosh's patch.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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".