Vick Khera wrote:
The drawback to partitioning by an ID number using modulo is that for constraint exclusion to work you have to actually add something like "AND (my_id % 42) = 0" to match the constraint. The exclusion is not done by executing the constraint, but by proving the constraint will hold true for the WHERE clause.
Vick's "Case study of partitioning a large table in Postgres 8" presentation at http://www.mailermailer.com/labs/presentations/index.rwp covers this topic in more detail. The important thing to realize is that your partitioning scheme must respect the limitations of the constraint exclusion code in the query planner to be most useful. You really need to consider not just the structure of the data, but what the queries against it look like, to make that call. If you can hack the application to include the modulo bit in every query that might be a sufficient breakdown for you. But you have to consider what that looks like from the query side, given what the optimizer knows how to do, not just the perspective of the table structure. Building a quick prototype and using EXPLAIN ANALYZE of common queries is invaluable here to do an early investigation of any potential redesign.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
