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'
-> 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)" -> 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)" -> 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)" -> 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.
VACUUM ANALYZEon 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.