Re: [GENERAL] Directing Partitioned Table Searches
On Fri, Feb 4, 2011 at 7:35 PM, Bill Thoen bth...@gisnet.com wrote: 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 The constraint exclusion code does not execute your constraints to decide whether to look at your partition; it examines the query and the constraint and does a proof to try to exclude the partition. If it cannot do that proof, it will scan that table. I'd like to be able to run some queries w/o the overhead of searching partitions unnecessarily. Can it be done? Your best bet is to know which partition you need and write your query that way dynamically, rather than trying to use a generic query and have the DB do the constraint exclusion. In your above case, if you know that 'zone' will limit you to just the MI table, then specify the MI table instead of the base clu table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Directing Partitioned Table Searches
Vick Khera wrote: Your best bet is to know which partition you need and write your query that way dynamically, rather than trying to use a generic query and have the DB do the constraint exclusion. In your above case, if you know that 'zone' will limit you to just the MI table, then specify the MI table instead of the base clu table. Thanks. That sounds simple enough. Since I want to automate this, I guess the next step is to learn how to create and execute a dynamic query. I think I know how to do that. -- *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 bth...@gisnet.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Directing Partitioned Table Searches
On Mon, Feb 7, 2011 at 1:17 PM, Bill Thoen bth...@gisnet.com wrote: Thanks. That sounds simple enough. Since I want to automate this, I guess the next step is to learn how to create and execute a dynamic query. I think I know how to do that. In perl, it looks something like this: $part = compute_partition($value); $sth = $dbh-prepare(select * from table_$part where foo=?); $sth-execute($value); you just interpolate the parts you need in the query string. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Directing Partitioned Table Searches
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 bth...@gisnet.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
