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: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Rafia Sabih <rafia.sabih@enterprisedb.com>, 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-20T02:02:40Z
Lists: pgsql-hackers 
On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Thu, Jul 20, 2017 at 7:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> 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.
>
> Isn't this the same as the issue reported here?
>
> https://www.postgresql.org/message-id/flat/CAEepm%3D270ze2hVxWkJw-5eKzc3AB4C9KpH3L2kih75R5pdSogg%40mail.gmail.com

Hmm, possibly.  But why would that affect the partition-wise join case only?

-- 
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".