Re: Directing Partitioned Table Searches

On Fri, Feb 4, 2011 at 7:35 PM, Bill Thoen <bthoen(at)gisnet(dot)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.