Postgres 9.1 query plan not using check_constraints on partitions after large data load I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip code. So I have a 'markets' table and 10 inherited tables such as markets_0, markets_1, etc. 

My constraint for each of the inherited table looks like such:

CONSTRAINT markets_0_zip_check CHECK ("left"(zip::text, 1) = '0'::text)
CONSTRAINT markets_1_zip_check CHECK ("left"(zip::text, 1) = '1'::text)


and so forth, for child tables markets_0 through markets_9. I also have an insert trigger that handles storing the data in the proper table. 

I loaded a few millions rows and ran EXPLAIN on the queries and everything looked fine when I queried on zip code. The query planner went straight to the appropriate table. However, after loading about 12 million rows of data, the query planner is now looking at all 10 tables, even though I'm querying on the zip code.

For example, a query such as this:

EXPLAIN select * from markets where zip='75254' 


produces this:

    -&gt;  Seq Scan on markets  (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)"
          Filter: ((zip)::text = '75254'::text)"
    -&gt;  Index Scan using index_markets_0_on_zip on markets_0 markets  (cost=0.00..8.52 rows=1 width=118) (actual time=0.021..0.021 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
    -&gt;  Index Scan using index_markets_1_on_zip on markets_1 markets  (cost=0.00..8.54 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
   -&gt;  Index Scan using index_markets_2_zip on markets_2 markets  (cost=0.00..8.39 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
          Index Cond: ((zip)::text = '75254'::text)


and so forth for each of the 9 tables.

So here is what I've done to trouble-shoot this:


I've verified that SET constraint_exclusion = partition; is enabled in my postgresql.conf file. I've tried setting this in the query itself with no difference in the results.
I've verified that there are no overlapping constraints or zip codes that don't start with 0-9.
I've verified that the data is being stored in each table properly.
I've run VACUUM ANALYZE on each of the tables; just in case.


I have other partitioned tables that are working fine on the same server, so I'm stumped as to why this is no longer working.

Any ideas?
A couple comments given the answer here.  First PostgreSQL currently does not check the where clause functions to see if all equality constraints can be met properly on all tables.  So you have to specify the condition for the partition often manually in the where clause.

A major point I would make here would be that if you are not doing a lot of frequent bulk operations, you may find that partitioning is not a net win here.  You may find that partial indexes get you what you want in a more flexible manner.  For example you could have a series of partial indexes:

CREATE INDEX markets_0_idx markets (id) WHERE zip between '00000' AND '09999';


and so forth.  However for a single zip-code lookup you are probably best to have just an index on zip.  Premature optimization is the root of all evil.