Re: On partitioning

El 12/12/2014 23:09, "Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com> escribió:
>
> Claudio Freire wrote:
>
> > 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.
>
> Arbitrary SQL expressions (including functions) are not the thing to use
> for partitioning -- at least that's how I understand this whole
> discussion. I don't think you want to do "proofs" as such -- they are
> expensive.
>
> To make this discussion a bit clearer, there are two things to
> distinguish: one is routing tuples, when an INSERT or COPY command
> references the partitioned table, into the individual partitions
> (ingress); the other is deciding which partitions to read when a SELECT
> query wants to read tuples from the partitioned table (egress).
>
> On ingress, what you want is something like being able to do something
> on the tuple that tells you which partition it belongs into. Ideally
> this is something much lighter than running an expression; if you can
> just apply an operator to the partitioning column values, that should be
> plenty fast. This requires no proof.
>
> On egress you need some direct way to compare the scan quals with the
> partitioning values. I would imagine this to be similar to how scan
> quals are compared to the values stored in a BRIN index: each scan qual
> has a corresponding operator strategy and a scan key, and you can say
> "aye" or "nay" based on a small set of operations that can be run
> cheaply, again without any proof or running arbitrary expressions.

Interesting that you mention BRIN. It does seem that it could be made to
work with BRIN's operator classes.

In fact, a partition-wide brin tuple could be stored per partition and that
in itself could be the definition for the partition.

Either preinitialized or dynamically updated. Would work even for arbitrary
routing functions, especially if the operator class to use is customizable.

I stand corrected.