Re: On partitioning

On Fri, Dec 12, 2014 at 7:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
>> On Fri, Dec 12, 2014 at 6:48 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> I have very little idea what the API you're imagining would actually
>>> look like from this description, but it sounds like a terrible idea.
>>> We don't want to make this infinitely general. We need a *fast* way
>>> to go from a value (or list of values, one per partitioning column) to
>>> a partition OID, and the way to get there is not to call arbitrary
>>> user code.
>
>> I think this was mentioned upthread, but I'll repeat it anyway since
>> it seems to need repeating.
>
>> More than fast, you want it analyzable (by the planner). Ie: it has to
>> be easy to prove partition exclusion against a where clause.
>
> Actually, I'm not sure that's what we want. I thought what we really
> wanted here was to postpone partition-routing decisions to runtime,
> so that the behavior would be efficient whether or not the decision
> could be predetermined at plan time.
>
> This still leads to the same point Robert is making: the routing
> decisions have to be cheap and fast. But it's wrong to think of it
> in terms of planner proofs.

You'll need proofs whether at the planner or at the execution engine.

A sequential scan over a partition with a query like

select * from foo where date between X and Y

Would be ripe for that but at some point you need to prove that the
where clause excludes whole partitions. Be it at runtime (while
executing the sequential scan node) or planning time.