In a partitioned table, is it possible to specify the partition for a
query to search using a variable instead of a constant?
EXAMPLE: Join another table to the partitioned one
Table: clu (partitioned by state)
ogc_fid bigint
cluid char(16)
state bpchar(2)
constraint: state='mi' (or 'co', 'ks', etc. for each partition)
Table: farms
link bigint
zone bpchar(2)
farmid char(7)
...
This selection will result in partitions being searched
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link
This selection will result in only the 'mi' (Michigan) partition being
searched
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link
WHERE state='mi'
And this selection will result in ALL partitions being searched. But why?
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link
WHERE state=zone
I'd like to be able to run some queries w/o the overhead of searching
partitions unnecessarily. Can it be done?
Regards,
--
*Bill Thoen*
GISnet - www.gisnet.com <http://www.gisnet.com/>
1401 Walnut St., Suite C
Boulder, CO 80302
303-786-9961 tel
303-443-4856 fax
bthoen@xxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
