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@xxxxxxxxx> Sent: Wednesday, November 4, 2020 9:13 PM To: Ehrenreich, Sigrid <Ehrenreich@xxxxxxxxxx> Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx Subject: Re: Partition pruning with joins On Wed, 4 Nov 2020 at 02:20, Ehrenreich, Sigrid <Ehrenreich@xxxxxxxxxx> 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
