Re: On partitioning - Mailing list pgsql-hackers

<p><br /> El 12/12/2014 23:09, "Alvaro Herrera" <<a
href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>escribió:<br /> ><br /> > Claudio Freire
wrote:<br/> ><br /> > > Fair enough, but that's not the same as not requiring easy proofs. The<br /> > >
plannermight not the one doing the proofs, but you still need proofs.<br /> > ><br /> > > Even if the
provingmethod is hardcoded into the partitioning method,<br /> > > as in the case of list or range partitioning,
it'sstill a proof. With<br /> > > arbitrary functions (which is what prompted me to mention proofs) you<br />
>> can't do that. A function works very well for inserting, but not for<br /> > > selecting.<br /> >
><br/> > > I could be wrong though. Maybe there's a way to turn SQL functions<br /> > > into analyzable
things?But it would still be very easy to shoot<br /> > > yourself in the foot by writing one that is too
complex.<br/> ><br /> > Arbitrary SQL expressions (including functions) are not the thing to use<br /> > for
partitioning-- at least that's how I understand this whole<br /> > discussion.  I don't think you want to do
"proofs"as such -- they are<br /> > expensive.<br /> ><br /> > To make this discussion a bit clearer, there
aretwo things to<br /> > distinguish: one is routing tuples, when an INSERT or COPY command<br /> > references
thepartitioned table, into the individual partitions<br /> > (ingress); the other is deciding which partitions to
readwhen a SELECT<br /> > query wants to read tuples from the partitioned table (egress).<br /> ><br /> > On
ingress,what you want is something like being able to do something<br /> > on the tuple that tells you which
partitionit belongs into.  Ideally<br /> > this is something much lighter than running an expression; if you can<br
/>> just apply an operator to the partitioning column values, that should be<br /> > plenty fast.  This requires
noproof.<br /> ><br /> > On egress you need some direct way to compare the scan quals with the<br /> >
partitioningvalues.  I would imagine this to be similar to how scan<br /> > quals are compared to the values stored
ina BRIN index: each scan qual<br /> > has a corresponding operator strategy and a scan key, and you can say<br />
>"aye" or "nay" based on a small set of operations that can be run<br /> > cheaply, again without any proof or
runningarbitrary expressions.<p>Interesting that you mention BRIN. It does seem that it could be made to work with
BRIN'soperator classes.<p>In fact, a partition-wide brin tuple could be stored per partition and that in itself could
bethe definition for the partition.<p>Either preinitialized or dynamically updated. Would work even for arbitrary
routingfunctions, especially if the operator class to use is customizable.<p>I stand corrected.<br />