Re: Partitioned Database and Choosing Subtables

On Mar 14, 2011, at 8:30 PM, Bill Thoen wrote:

> I've got a ver 8.4.5 partitioned data base with records organized by US state, so the partitions are set up by state. When I query this database and include the key field that tells postgres what partition you , everything works as I expect. It searches only the specified partition, and it's fast . But that's only if I use a constant, like this:
>
> SELECT lions, tigers, bears FROM WildLife
> WHERE state_pt = 'CO';
>
> What I want to be able to do is put this key value in a table and PG look in whatever partition the column specifies, like so:
>
> SELECT lions, tigers, bears, statecode FROM WildLife
> WHERE state_pt = statecode;
>
> However when I try anything other than a constant, it search EVERY partition, sequentially, which is not what I want it to do. So is there any way to specify the partition to search using a variable/column name?

To answer the specific question you ask, you can always tell Postgres to search a particular child table:

SELECT lions, tigers, bears FROM Wildlife_CA ...

But your example seems somewhat unclear to me. Are both "state_pt" and "statecode" columns in Wildlife? If so, Postgres is going to have to search every partition, because it can't just from the partition constraint know which entries will match and which do not until it looks inside every record.

Or did you mean 'statecode' to be a column in a different table, on which you're joining?

--
-- Christophe Pettus
xof(at)thebuild(dot)com