Re: On partitioning

On Fri, Dec 12, 2014 at 7:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 12/12/2014 02:10 PM, Tom Lane wrote:
>> 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.
>
> The other reason I'd really like to have the new partitioning taken out
> of the planner: expressions.
>
> Currently, if you have partitions with constraints on, day,
> "event_date", the following WHERE clause will NOT use CE and will scan
> all partitions:
>
> WHERE event_date BETWEEN ( '2014-12-11' - interval '1 month' ) and
> '2014-12-11'.
>
> This is despite the fact that the expression above gets rewritten to a
> constant by the time the query is executed; by then it's too late. To
> say nothing of functions like to_timestamp(), now(), etc.
>
> As long as partitions need to be chosen at plan time, I don't see a good
> way to fix the expression problem.

Fair enough, but that's not the same as not requiring easy proofs. The
planner might not the one doing the proofs, but you still need proofs.

Even if the proving method is hardcoded into the partitioning method,
as in the case of list or range partitioning, it's still a proof. With
arbitrary functions (which is what prompted me to mention proofs) you
can't do that. A function works very well for inserting, but not for
selecting.

I could be wrong though. Maybe there's a way to turn SQL functions
into analyzable things? But it would still be very easy to shoot
yourself in the foot by writing one that is too complex.