Horton

Re: [HACKERS] Runtime Partition Pruning

Jesper Pedersen <jesper.pedersen@redhat.com>

View thread View on postgr.es
From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Beena Emerson <memissemerson@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Robert Haas <robertmhaas@gmail.com>, amul sul <sulamul@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2018-04-04T17:31:31Z
Lists: pgsql-hackers 
Hi David,

On 04/03/2018 10:10 PM, David Rowley wrote:
>> The attached case doesn't trigger a generic plan, so basically all time is
>> spent in GetCachedPlan.
> 
> Yeah, there's still no resolution to the fact that a generic plan +
> runtime pruning might be cheaper than a custom plan.  The problem is
> the generic plan appears expensive to the custom vs generic plan
> comparison due to it containing more Append subnodes and the run-time
> pruning not being taking into account by that comparison.
> 
> There's been some discussion about this on this thread somewhere.
>

Forgot about that, sorry.

> I think the best solution is probably the one suggested by Robert [1]
> and that's to alter the Append plan's cost when run-time pruning is
> enabled to try to account for the run-time pruning. This would be a
> bit of a blind guess akin to what we do for clause selectivity
> estimates for Params, but it's probably better than nothing, and
> likely better than doing nothing.
>

Yeah, something based on the number of WHERE clauses, and if the 
partition type has DEFAULT / NULL partition could help. Forcing 
choose_custom_plan() to return false does help a lot (> 400%) for the 
HASH case.

But maybe this area is best left for PG12.

> Yeah, it's a bug in v46 faster partition pruning. Discussing a fix for
> that with Amit over on [2].
>

I was running with a version of faster_part_prune_v45_fixups.patch.

Patch v49 with v18 (0001-0004) works. 0005 needs a rebase.

Thanks again,
  Jesper

Commits

  1. Initialize ExprStates once in run-time partition pruning

  2. Add bms_prev_member function

  3. Support partition pruning at execution time

  4. Document partprune.c a little better

  5. Fix crash in pg_replication_slot_advance

  6. Add parallel-aware hash joins.

  7. Fix typo.

  8. Allow --with-bonjour to work with non-macOS implementations of Bonjour.

  9. Mention need for --no-inc-recursive in rsync command