Re: Partitioned Database and Choosing Subtables - Mailing list pgsql-general

On 3/15/2011 12:02 PM, Igor Neyman wrote:
>
>
>> -----Original Message-----
>> From: Bill Thoen [mailto:bthoen@gisnet.com]
>> Sent: Monday, March 14, 2011 11:31 PM
>> To: pgsql-general@postgresql.org
>> Subject: Partitioned Database and Choosing Subtables
>>
>> 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?
>>
>> --
>> *Bill Thoen*
>> GISnet - www.gisnet.com
>> 303-786-9961
>>
> Try using "dynamic" sql:
>
> EXECUTE 'SELECT lions,  tigers, bears, statecode FROM WildLife WHERE
> state_pt = ' || statecode INTO ...;
>
> See:
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html
>
Thanks Igor. It was a nice try -- and I thought it would work, but the
Planner had other plans. Basically, I tried the interactive method using
a PREPARE statement with one text parammeter followed by an EXECUTE
statement. Unfortunately, the query still went rummaging across the
entire database sequentially.

To recap, (I still hope there's a chance for a workable answer) here's a
simple contrived scenario

Incidents Table
-----------
total  integer,          -- total number of human:critter encounters
statecode char(2),       -- by state
outcome char(20)         -- outcome of incident {fatal, serious, minor,
close call}


Wildlife Table           -- the Partitioned table. Uses state_pt to
separate data tables.
                             table  1: state_pt='AK'
                             table  2: state_pt='AL'
                             ...
                             table 50: state_pt='WY'
-----------------
lions integer,           -- total population of lions in the state
tigers integer,          -- total tigers
bears integer,           -- total bears
outcome char(20),        -- outcome of incident {fatal, serious, minor,
close call}
... more attributes...
state_pt                 -- key index that separates the sub tables
inthe partitioned datbase


Some incidents table data:

total |   statecode  | outcome
------+--------------+-------------
    7  |     CA       |  fatal
   12  |     CA       |  close call
    3  |     CO       |  fatal
   16  |     CO       |  close call
    4  |     WY       |  minor



If I do the following it finds the data and goes directly to the right
table.

     SELECT lions, tigers, bears FROM Wildlife
     WHERE state_pt = 'CO';

Or this:
\set theState '\''CO'\''


     SELECT lions, tigers, bears FROM Wildlife
     WHERE state_pt = :theState ;

It works perfectly. But when I tried this:

PREPARE foo (text) AS
     SELECT lions, tigers, bears FROM Wildlife
     WHERE state_pt = $1;

EXECUTE foo( 'CO' )

It sequentially scanned everything, so I didn't even try the scenario
I'm trying to create, which is using the results of a select to supply
the key code to the Planner as to what partition it should search for
reach record.

SELECT lions, tigers , bears FROM Wildlife a JOIN incidents b ON
a.outcome=b.outcome
WHERE state_pt=statecode;

Apparently, the Planner is pretty fussy about how you refer to one of
your subtables, but I almost always use queries that involve multiple
states,and the knowledge of what state table a particular record needs
to access is carried as one of that record's attributes. I'm surprised
that there's so little info on this. I tseems like an obvious
application  of partitioned databases, but maybe I just don't understand
how it should be used?

It seems that the only variables it likes are host variables, and there
maybe something down that road...
Anywya the quuestion is still open if anyone has some good ideas.