Thanks for the benchmarking results!
On Tue, Aug 15, 2017 at 11:35 PM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:
> Q4 | 244 | 12 | PA and PWJ, time by only PWJ - 41
12 seconds instead of 244? Whoa. I find it curious that we picked a
Parallel Append with a bunch of non-partial plans when we could've
just as easily picked partial plans, or so it seems to me. To put
that another way, why did we end up with a bunch of Bitmap Heap Scans
here instead of Parallel Bitmap Heap Scans?
> Q7 | 134 | 88 | PA only
> Q18 | 508 | 489 | PA only
What's interesting in these results is that the join order changes
quite a lot when PA is in the mix, and I don't really see why that
should happen. I haven't thought about how we're doing the PA costing
in a while, so that might just be my ignorance. But I think we need
to try to separate the effect of the plan changes from the
execution-time effect of PA itself, so that we can (1) be sure that
the plan changes are legitimate and justifiable rather than the result
of some bug and (2) make sure that replacing an Append with a Parallel
Append with no other changes to the plan produces an execution-time
benefit as we're hoping.
> Q21 | 649 | 163 | PA only
This is a particularly interesting case because in both the patched
and unpatched plans, the driving scan is on the lineitem table and in
both cases a Parallel Seq Scan is used. The join order is more
similar than in some of the other pans, but not the same: in the
unpatched case, it's l1-(nation-supplier)-l2-orders-l3; in the patched
case, it's l1-(nation-supplier)-l3-l2-orders. The Parallel Append
node actually runs slower than the plan Append node (42.4 s vs. 39.0
s) but that plan ends up being faster overall. I suspect that's
partly because the patched plan pulls 265680 rows through the Gather
node while the unpatched plan pulls 2888728 rows through the Gather
node, more than 10x more. That's a very strange choice for the
planner to make, seemingly, and what's even stranger is that if it did
ALL of the joins below the Gather node it would only need to pull
78214 rows through the Gather node; why not do that?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company