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.
WhenI 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,
likeso:
>
> 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
itto 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,
Postgresis going to have to search every partition, because it can't just from the partition constraint know which
entrieswill 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@thebuild.com