Re: Partitioned tables - planner wont use indexes - Mailing list pgsql-performance

From paul rivers
Subject Re: Partitioned tables - planner wont use indexes
Date
Msg-id 47F6D0E5.2050005@gmail.com
Whole thread Raw
In response to Partitioned tables - planner wont use indexes  (kevin kempter <kevin@kevinkempterllc.com>)
List pgsql-performance
kevin kempter wrote:
> Hi List;
>
> Sorry if this is a dupe, my first post never showed up...
>
> I'm having some performance issues with a partitioned table. We have a
> VERY large table that we've partitioned by day.
>

Unfortunately, that is the defined behavior in this case. From 5.9.6 of
the manual:

"Constraint exclusion only works when the query's WHERE clause contains
constants."

[Where the constants are of course your partitioning column(s)]


The best way around this depends mostly on what you're up to. You can
get the min tablename from the catalogs, or you can keep a table of
active partitions that your script which drops off old partitions and
generates new ones can keep updated on the oldest/newest partition
dates. Or some number of other solutions, whatever you find cleanest for
your purposes.

Paul



pgsql-performance by date:

Previous
From: kevin kempter
Date:
Subject: Partitioned tables - planner wont use indexes
Next
From: Gregory Stark
Date:
Subject: Re: Partitioned tables - planner wont use indexes