Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

    ->  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.

  • 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?

share|improve this question
2  
What do you get if you use/add "left"(zip::text, 1) = '1'::text in the WHERE clause? –  dezso Jan 17 '13 at 20:01
    
@dezso Ahh..Yeah, that does fix it. It only scans the proper market table. But any idea why Postgres won't "do that for me?". I'm using an ORM so it won't be easy to craft the query in that manner. –  Mark Hoffman Jan 17 '13 at 20:05
    
It's quite picky on the conditions for constraint exclusion, or at least won't try to translate the conditions into those used for partitioning. –  dezso Jan 17 '13 at 20:13
    
How many rows will the table have? A table with "a few million" rows might not necessarily need partitioning if indexed properly for the queries in question. –  a_horse_with_no_name Jan 29 '13 at 8:55
    
I suspect part of the problem is the use of a function. Functions are usually planner opaque so it is not surprising the planner can't use this for elimination. –  Chris Travers Jan 29 '13 at 13:51
add comment

1 Answer

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.

share|improve this answer
    
Interesting idea but I didn't managed to make it work as the query planner has the same kind of limitation with conditional indexes than with partitions. If you can't use explicit constants in your query, PG can't prepare which index to use and do a full sequential scan instead. With big tables, it's just worst. –  Le Droid Oct 30 '13 at 22:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.