Re: why partition pruning doesn't work?

On 8 June 2018 at 18:14, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 8 June 2018 at 15:22, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>>> On 8 June 2018 at 03:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> Maybe there's something I'm missing here, but I sort of hoped that this
>>>> patch would nuke all the special-case code for Params in this area.
>>>> Why is there any need to distinguish them from other stable expressions?
>>
>>> We need to know which Params exist in the Expr as if there are no
>>> Params, or only external Params, then we can run-time prune during
>>> startup of the executor.
>>
>> This does not refute my question. Why doesn't the same logic apply
>> to any stable expression? That is, ISTM a Param is a special case
>> of that.
>
> Okay, maybe we don't need to know which external params exist, but we
> need to know if there are any exec params so that we don't try to
> evaluate an expression with any of those during executor startup.
>
> I'll produce a patch which simplifies things in that area.

Okay, I've gotten rid of the tracking of external params. We now just
track exec params. We still need to know about these so we know if a
re-prune is required during ExecReScanAppend(). Obviously, we don't
want to prune on any random Param change, so I'm fairly sure it's a
good idea to keep track of these.

I've changed the code inside partkey_datum_from_expr so that it's a
simple bool array lookup to decide if we can evaluate the expression
or not. This bool array is populated during planning, which I think is
rather nice so we don't have to go and do it each time the plan is
executed.

I also discovered that I was needlessly running the pruning code again
during executor run in some cases where there was no possibility of
doing any further pruning there. I've had to add some new code to set
the present_parts inside ExecFindInitialMatchingSubPlans(). It now
properly removes the member of any sub-partitions which have had all
of their partitions pruned. This allows us just to use 'present_parts'
to calculate the subnodes from, rather than going and calling the
pruning code again.

Technically PartitionPruningData does not really need the
do_exec_prune field. A non-empty execparams could indicate this, but I
felt it was better to have the bool so that we have one for each
method of run-time pruning. This also saves a bms_is_empty() call
inside find_subplans_for_params_recurse(). This could be a bit of a
hotspot during parameterized nested loops which cause partition
pruning.

I'm really hoping this is what you meant about the special-case code for Params.

Does this look any better?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services