Re: inconsistent results querying table partitioned by date

On 2019/05/17 7:50, Tom Lane wrote:
> I wrote:
>> In other words, as I compose this I'm talking myself into the
>> idea that invoking gen_partprune_steps three times (for planner,
>> executor startup, and executor runtime, with corresponding
>> filters on which clauses can be used) is the only safe near-term
>> fix. In the future we can look at cutting down the repetitive
>> costs that entails.
>
> Here's a really quick and dirty POC along that line.

Thanks for the patch.

> It's unfinished
> but I'm out of time for today. I think the executor side of it is OK,
> but the planner side is crufty: it lacks logic to decide whether
> either the initial pruning or exec pruning steps are unnecessary.
> And I didn't update most of the comments either.
>
> I think the rule for "exec pruning is unnecessary" could probably
> just be "we didn't find any exec params in the prune steps", since
> that would imply that the initial steps include everything.
> I don't see an equally cheap way to decide that initial pruning
> is useless, though. Thoughts?

As promised, here is the idea I was thinking of, although I'm no longer
confident you will like it based on your previous emails on this thread.
I was thinking of having a walker function that traverses the list of
*clauses* that were passed to make_partition_pruneinfo(), which puts the
paramids into an output context struct, with separate Bitmapsets for
PARAM_EXTERN and PARAM_EXEC parameters. In addition to running this
walker, also do a contain_mutable_functions() pass over the clauses. (I
can already hear a big no at this point!) As you might've guessed, the
result of those two steps would decide whether we need to do the the extra
gen_partprune_steps() steps. Fwiw, I prototyped it in the attached patch
which applies on top of yours.

Maybe as the only defense, I'd say that that would be cheaper in the
fairly common case where no run-time pruning is needed than doing two
full-blown gen_partprune_steps().

> Also, it seems like we could save at least some of the planning expense by
> having the PARTCLAUSE_INITIAL pass report back whether it had discarded
> any Param-bearing clauses or not. If not, there's definitely no need to
> run the PARTCLAUSE_EXEC pass.

That sounds like a good idea.

Thanks,
Amit