RE: Partition pruning with joins

Hi David,

Thanks a lot for your response.

> If there was some way to make a parameterised nested loop more favourable, then that might help you.
Setting enable_hashjoin=OFF sped up the execution, but unfortunately we have not means to inject this into to application ☹

> That would require an index on fact(part_key),
I have tried this with our real data, but the index is not used (because another condition in our where clause is chosen to filter the data instead of the part_key). I have left this out of my posted testcase, to keep down the complexity of the testcase (I hope this is understandable, English is not my native language 😉).

Regards,
Sigrid

-----Original Message-----
From: David Rowley <dgrowleyml(at)gmail(dot)com>
Sent: Wednesday, November 4, 2020 9:13 PM
To: Ehrenreich, Sigrid <Ehrenreich(at)consist(dot)de>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: Partition pruning with joins

On Wed, 4 Nov 2020 at 02:20, Ehrenreich, Sigrid <Ehrenreich(at)consist(dot)de> wrote:
>
> -- Statement
> explain SELECT
> count(*)
> FROM
> dim INNER JOIN fact ON (dim.part_key=fact.part_key)
> WHERE dim.part_key >= 110 and dim.part_key <= 160;
>
> Plan shows me, that all partitions are scanned:
> Aggregate (cost=461.00..461.01 rows=1 width=8)
> -> Hash Join (cost=4.64..448.25 rows=5100 width=0)
> Hash Cond: (fact.part_key = dim.part_key)
> -> Append (cost=0.00..390.00 rows=20000 width=4)
> -> Seq Scan on fact_100 fact_1 (cost=0.00..145.00 rows=10000 width=4) ⇐==== unnecessarily scanned
> -> Seq Scan on fact_200 fact_2 (cost=0.00..145.00 rows=10000 width=4)
> -> Hash (cost=4.00..4.00 rows=51 width=4)
> -> Seq Scan on dim (cost=0.00..4.00 rows=51 width=4)
> Filter: ((part_key >= 110) AND (part_key <= 160))
>
>
> I know, that I could get rid of this problem, by rewriting the query to include the partitioned table in the where clause like this:
> WHERE fact.part_key >= 210 and fact.part_key <= 260
> Partition pruning happens very nicely then.

It sounds like what I mentioned in [1] would be the best way to
optimise this. Unfortunately, the idea didn't get much support and I
didn't pursue it any further.

I think it would also be possible to perform run-time pruning on each
row from the inner side of the join and bitmap-OR the matching
partitions each time then just scan those ones when performing the
probe phase of the hash join. However, in practice, I'm not too sure
how that could be made to work well since nodeHashjoin.c would have to
be in charge of collecting the matching partitions when building the
hash table, but nodeAppend.c would have to be in charge of skipping
partitions that can't have any matches. I'm unsure how exactly the
hash join would communicate that to the Append. Traditionally, each
node is oblivious to its children nodes.

I imaging the overheads of performing run-time pruning for each inner
row might be quite high for this. We do something like this for
parameterised nested loop joins, but generally those are only chosen
when the number of lookups is relatively small. And with Nested Loop,
the lookups are almost certainly much more expensive than a hash
probe, since they'll require scanning an index from the root each time
we get a new parameter.

> Unfortunately this is not an option for us, because the code in our case is generated by some third party software (sigh).
>
> Do you have any suggestions, what else I could do? (Or maybe you could add it as a new feature for v14 )?

If there was some way to make a parameterised nested loop more
favourable, then that might help you. That would require an index on
fact(part_key), but I imagine it's unlikely to work for you as the
benefits of run-time pruning are not really costed into the plan, so
it may be unlikely that you'll coax the planner into choosing that
plan. It may be an inferior plan anyway.

David

[1] https://www.postgresql.org/message-id/flat/30810.1449335261%40sss.pgh.pa.us#906319f5e212fc3a6a682f16da079f04