On 12/13/2014 05:57 PM, José Luis Tallón wrote:
On 12/13/2014 03:09 AM, Alvaro Herrera wrote:
[snip]
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.
Yup. Plus, it looks like (from reading Oracle's documentation) they
end up converting the LESS THAN clauses into range lists internally.
Anyone that can attest to this? (or just disprove it, if I'm wrong)
I just suggested using the existing RangeType infrastructure for this
( <<, >> and && operators, specifically, might do the trick) before
reading your mail citing BRIN.
... which might as well allow some interesting runtime
optimizations when range partitioning is used and *a huge* number of
partitions get defined --- I'm specifically thinking about massive
OLTP with very deep (say, 5 years' worth) archival partitioning where
it would be inconvenient to have the tuple routing information always
in memory.
I'm specifically suggesting some ( range_value -> partitionOID)
mapping using a BRIN index for this --- it could be auto-created just
like we do for primary keys.
Reviewing the existing documentation on this topic I have stumbled on an
e-mail by Simon Riggs from almost seven years ago
http://www.postgresql.org/message-id/1199296574.7260.149.ca...@ebony.site
.... where he suggested a way of physically partitioning tables by using
segments in a way that sounds to be quite close to what we are proposing
here.
ISTM that the partitioning meta-data might very well be augmented a bit
in the direction Simon pointed to, adding support for "effectively
read-only" and/or "explicitly marked read-only" PARTITIONS (not segments
in this case) for an additional optimization. We would need some syntax
additions (ALTER PARTITION <name> SET READONLY) in this case.
This feature can be added later on, of course.
I'd like to explicitly remark the potentially performance-enhancing
effect of fillfactor=100 (cfr.
http://www.postgresql.org/docs/9.3/static/sql-createtable.html) and
partitions marked "effectively read-only" (cfr. Simon's proposal) when
coupled with "fullscan analyze" vs. the regular sample-based analyze
that autovacuum performs.
When a partition consists of multiple *segments*, a generalization of
the proposed BRIN index (to cover segments in addition to partitions)
will further speed up scans.
Just for the record, allowing some partitions to be moved to foreign
tables (i.e. foreign servers, via postgres_fdw) will multiply the
usefullness of this "partitioned table wide" BRIN index .... now
becoming a real "global index".
Just my 2c
Thanks,
/ J.L.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
