Re: [HACKERS] Runtime Partition Pruning

Hello David,


On Thu, Dec 21, 2017 at 2:31 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 19 December 2017 at 21:54, Beena Emerson <memissemerson@gmail.com> wrote:

> The problem is down to the logic in choose_custom_plan() only choosing
> a generic plan if the average cost of the generic plan is less than
> the average custom plan cost. The problem is that the generic plan can
> have many extra Append subnodes in comparison to the custom plan, all
> of which are taken into account in the total plan cost, but these may
> be pruned during execution. The logic in choose_custom_plan() has no
> idea about this.  I don't have any bright ideas on how to fix this
> yet, as, suppose a PREPAREd statement like the following comes along:
>
> PREPARE q3 (int, int) AS SELECT * FROM partitioned_table WHERE partkey
> BETWEEN $1 AND $2;
>
> the run-time pruning may prune it down no subplans, all subplans, or
> any number in between. So we can't do anything like take the total
> Append cost to be the highest costing of its subplans, and likely
> using the average cost might not be a good idea either. It might work
> sometimes, but likely won't be very stable. If this is not fixed then
> choose_custom_plan() has a very low probability of choosing a generic
> plan which has run-time partition pruning enabled, which in a way
> defeats the purpose of this whole patch.
>
> I'm a bit uncertain on the best way to resolve this. It needs to be
> discussed here.

I had mentioned this in the first mail on this thread that the generic
plan is always preferred and Robert said that it is not in scope of
this patch. Maybe we can start a new thread for this.

>
> One more thing. The attached is not yet set up to work with
> MergeAppend. It's likely just a small amount of additional work to
> make this happen, so likely should be something that we do.
>
> Anyway, I've attached the latest version of the patch. This is based
> on Amit's v15 of faster-partition-pruning [1] which I found to cleanly
> apply to f94eec490

Thank you for working on this. I  will look into this and merge with
my current version of patch and Amit's v16 patches and post a new
patch soon.


-- 

Beena Emerson

EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company