From: Kong Mansatiansin <kong@mongonet.net>
To: Willy-Bas Loos <willybas@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Query Optimization with Partitioned Tables
Date: Thu, 02 Sep 2010 09:56:57 -0700
Message-ID: <1283446617.18966.37.camel@annapurna.mongonet.net> (raw)
In-Reply-To: <AANLkTinnxSijJYRP0aq4XY=ta8iF60C+qQF5X0Edc-9F@mail.gmail.com>
References: <1283370592.1084.36.camel@annapurna.mongonet.net>
<AANLkTinnxSijJYRP0aq4XY=ta8iF60C+qQF5X0Edc-9F@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kong@mongonet.net, willybas@gmail.com
Subject: Re: Query Optimization with Partitioned Tables
In-Reply-To: <1283446617.18966.37.camel@annapurna.mongonet.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox