Re: Partition-wise join for join between (declaratively) partitioned tables

On Fri, Jul 21, 2017 at 11:42 AM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
>
> Following the discussion at [1], with the patch Thomas posted there, now Q21
> completes in some 160 seconds.

Your earlier reports mentioned unpartitioned case taking 300 seconds,
partitioned case without partition-wise join taking 600 seconds and
with partition-wise join it taking 3200 seconds. My experiements
showed that those have changed to 70s, 160s and 160s resp. This is
with Thomas's patch. Can you please confirm?

> The plan is changed for the good but does not
> use partition-wise join.

As explained earlier, this is because the tables are not partitioned
similarly. Please try with lineitem and orders partitioned similarly
i.e. same number of partitions and exactly same ranges.

> Not just the join orders but the join strategy itself changed, with the
> patch no hash semi join is picked which was consuming most time there,
> rather nested loop semi join is in picture now, though the estimates are
> still way-off, but the change in join-order made them terrible from
> horrible. It appears like this query is performing efficient now
> particularly because of worse under-estimated hash-join as compared to
> under-estimated nested loop join.

Earlier it was using partition-wise join between lineitems (l1, l2,
l3) since it's the same table. Now for some reason the planner doesn't
find joining them to each other a better strategy, instead they are
joined indirectly so we don't see partition-wise join being picked. We
should experiment with orders and lineitems being partitioned
similarly. Can you please provide that result?

>
> For the hash-semi-join:
> -> Hash (cost=3449457.34..3449457.34 rows=119994934 width=8) (actual
> time=180858.448..180858.448 rows=119994608 loops=3)
> Buckets: 33554432
> Batches: 8 Memory Usage: 847911kB
>
> Overall, this doesn't look like a problem of partition-wise join patch
> itself.
>

Thanks for confirming it.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company