Re: On partitioning - Mailing list pgsql-hackers

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.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services