On Thu, Jul 20, 2017 at 12:30 AM, Robert Haas <robertmh...@gmail.com> wrote: > >> 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.
The patch does not have any changes to the selectivity estimation. It might happen that some correction in selectivity estimation for child-joins is required, but I have not spotted any code in selectivity estimation that differentiates explicitly between child and parent Vars and estimates. So, I am more inclined to believe Thomas's theory. I will try Tom's suggested approach. I am investigating this case with the setup that Rafia provided. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers