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
>