WBL, Unfortunately, with the way it originally designed, there is no column that our existing queries and views use as predicate to take advantage of constraint exclusion. That said, it might imply that these tables are not good candidates for partitioning in the first place. The only main purpose of this for me was to facilitate data pruning without having to deal with slow DELETE and blocking VACUUM processes. As a last resource, I was trying to see if I could direct the query plan to do what I think is the most optimal path. With join_collapse_limit=1, I was about to rearrange the join order but the query planner would still not use the only predicate (xid='...') to filter rows as the first selective step, despite the fact/stats that it could eliminate 26M rows from xids (xtx) into only one row (using ix_xids_1). Would you be able to advise me on my 2nd question about how to make the query plan more selective. (Not sure if you can easily view my original message with the lengthy query plan. I just don't want to clutter this email with repeated cryptic text, but please let me know I should include it on my next email.) Thank you much, -Kong On Thu, 2010-09-02 at 10:15 +0200, Willy-Bas Loos wrote: > > Note: I just learned from this change that with the 3x2 additional > > tables from the 2 new partitioned table the query of 8 becomes one with > > 14 joined tables > > It seems then that you did not partition your tables in such a way > that your query only needs to scan one of them? > That is where partitioning becomes worth wile: when you have to scan > fewer and smaller tables. > You need to have a check constraint that enforces the partitioning > attribute, and you need to set constraint_exclusion to true in > postgresql.conf (or to "partition" if you use 8.4). > Then in your query if you use a predicate that corresponds to the > check constraint, the planner will skip the other tables, since it > knows that the desired data cannot be in there. > > So for example if you make one table per month, you partition by the > field "the_date" (date). (make one empty parent table and inheriting > child tables) > You have a check constraint that says (the_date >= '20100101'::date > AND the_date < '20100201'::date) > Then, when you query for data in January of 2010 only this table would > be scanned. So that should not add any more tables to your query plan. > > maybe a bit basic, but i hop e it helps. > > cheers, > > WBL > -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
