On Wed, Jul 19, 2017 at 12:24 AM, Rafia Sabih <rafia.sa...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
