From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Jones <rj(at)metabrew(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Partitioned/inherited tables with check constraints causing slower query plans |
Date: | 2012-05-04 16:39:02 |
Message-ID: | 7297.1336149542@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Richard Jones <rj(at)metabrew(dot)com> writes:
> I'm seeing poor query performance using partitioned tables with check
> constraints, seems like the plan is much worse than when querying the
> individual partitions manually.
> select version(); --> PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu,
> compiled by gcc (Debian 4.4.5-8) 4.4.5, 64-bit
I get a reasonable-looking plan when I try to duplicate this issue in
9.1 branch tip. I think the reason you're not getting the right
behavior is that you are missing this as-yet-unreleased patch:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=ef03b34550e3577c4be3baa25b70787f5646c57b
which means it can't figure out that the available index on the child
table produces the desired sort order. If you're in a position to
compile from source, a current nightly snapshot of the 9.1 branch
ought to work for you; otherwise, wait for 9.1.4.
(Note: although that patch is a one-liner, I would *not* recommend
trying to just cherry-pick the patch by itself; I think it probably
interacts with other planner fixes made since 9.1.1.)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Jones | 2012-05-04 17:00:21 | Re: Partitioned/inherited tables with check constraints causing slower query plans |
Previous Message | Jeff Janes | 2012-05-04 16:30:57 | Re: Configuration Recommendations |