From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com> |
Cc: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Partition-wise join for join between (declaratively) partitioned tables |
Date: | 2017-07-19 19:00:21 |
Message-ID: | CA+TgmoY=77ferwzymHuvZgQDDv7acWpxYDH=_Js6e_hwk9oyFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Jul 19, 2017 at 12:24 AM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-07-19 19:02:44 | Re: GSoC 2017: Foreign Key Arrays |
Previous Message | Fabrízio de Royes Mello | 2017-07-19 18:59:14 | Re: Patch: Add --no-comments to skip COMMENTs with pg_dump |