On 3/15/2011 12:02 PM, Igor Neyman wrote:
-----Original Message-----
From: Bill Thoen [mailto:bthoen@xxxxxxxxxx]
Sent: Monday, March 14, 2011 11:31 PM
To: pgsql-general@xxxxxxxxxxxxxx
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
