I have a setup in which a table has been partitioned into 30 partitions on type (1 -30), however no matter what I do i can't make the planner try to use constraint exclusion on it. As you can see by the plan, it figures that there is at least 1 rows in each partition (Which there is not). Also yesterday when I was first looking into this the plan on partitons 28.29.30 were different (they were still 0 rows then too) it shows the estimated rows being 4.
(All the following were done after a fresh VACUUM ANALYZE) db=# SELECT version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) (1 row) db=# show constraint_exclusion; constraint_exclusion ---------------------- on (1 row) db=# \d tbl_ps Table "public.tbl_ps" Column | Type | Modifiers ----------------+-----------------------+------------------------------------------------------------------------- id | integer | not null default nextval('tbl_ps_id_seq'::regclass) uid | integer | normalized_txt | character varying(50) | type | smallint | lastlogin | integer | Indexes: "id_idx" btree (pse_id) Triggers: tbl_ps_partitioner BEFORE INSERT OR DELETE OR UPDATE ON tbl_ps FOR EACH ROW EXECUTE PROCEDURE tbl_ps_handler() db=# \d s_ps.tbl_ps_type_1 Table "s_ps.tbl_ps_type_1" Column | Type | Modifiers -----------------+-----------------------+------------------------------------------------------------------------- id | integer | not null default nextval('tbl_ps_id_seq'::regclass) uid | integer | normalized_text | character varying(50) | interest_type | smallint | lastlogin | integer | Indexes: "index_09_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= '0'::text AND normalized_text::text <= '9'::text "index_a_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'a'::text AND normalized_text::text < 'b'::text "index_b_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'b'::text AND normalized_text::text < 'c'::text "index_c_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'c'::text AND normalized_text::text < 'd'::text "index_cluster_on_part_1" btree (normalized_text, lastlogin) CLUSTER "index_d_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'd'::text AND normalized_text::text < 'e'::text "index_e_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'e'::text AND normalized_text::text < 'f'::text "index_f_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'f'::text AND normalized_text::text < 'g'::text "index_g_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'g'::text AND normalized_text::text < 'h'::text "index_h_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'h'::text AND normalized_text::text < 'i'::text "index_i_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'i'::text AND normalized_text::text < 'j'::text "index_id_on_type_1" btree (id) "index_j_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'j'::text AND normalized_text::text < 'k'::text "index_k_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'k'::text AND normalized_text::text < 'l'::text "index_l_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'l'::text AND normalized_text::text < 'm'::text "index_m_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'm'::text AND normalized_text::text < 'n'::text "index_n_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'n'::text AND normalized_text::text < 'o'::text "index_o_on_tupe_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'o'::text AND normalized_text::text < 'p'::text "index_p_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'p'::text AND normalized_text::text < 'q'::text "index_q_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'q'::text AND normalized_text::text < 'r'::text "index_r_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'r'::text AND normalized_text::text < 's'::text "index_s_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 's'::text AND normalized_text::text < 't'::text "index_t_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 't'::text AND normalized_text::text < 'u'::text "index_u_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'u'::text AND normalized_text::text < 'v'::text "index_v_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'v'::text AND normalized_text::text < 'w'::text "index_w_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'w'::text AND normalized_text::text < 'x'::text "index_x_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'x'::text AND normalized_text::text < 'y'::text "index_y_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'y'::text AND normalized_text::text < 'z'::text "index_z_on_type_1" btree (normalized_text, lastlogin) WHERE normalized_text::text >= 'z'::text "index_uid_on_part_1" btree(uid) Check constraints: "tbl_ps_typ_1_type_check" CHECK (type = 1) Inherits: tbl_ps db=# myyearbook=# EXPLAIN ANALYZE SELECT uid FROM tbl_ps WHERE type = 1 and normalized_text='bush'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- Result (cost=0.00..130.71 rows=60 width=4) (actual time=0.135..99.474 rows=682 loops=1) -> Append (cost=0.00..130.71 rows=60 width=4) (actual time=0.131..97.205 rows=682 loops=1) -> Seq Scan on tbl_ps (cost=0.00..2.27 rows=1 width=4) (actual time=0.045..0.045 rows=0 loops=1) Filter: ((type = 1) AND ((normalized_text)::text = 'bush'::text)) -> Index Scan using index_b_on_type_1 on tbl_ps_type_1 tbl_ps (cost=0.00..97.81 rows=705 width=4) (actual time=0.080..4.331 rows=682 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_2 on tbl_ps_type_2 tbl_ps (cost=0.00..4.53 rows=1 width=4) (actual time=0.099..0.099 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_3 on tbl_ps_type_3 tbl_ps (cost=0.00..4.49 rows=1 width=4) (actual time=0.062..0.062 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (ptype = 1) -> Index Scan using index_b_on_type_4 on tbl_ps_type_4 tbl_ps (cost=0.00..4.44 rows=1 width=4) (actual time=0.057..0.057 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_5 on tbl_ps_type_5 tbl_ps (cost=0.00..4.41 rows=1 width=4) (actual time=0.056..0.056 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_6 on tbl_ps_type_6 tbl_ps (cost=0.00..4.36 rows=1 width=4) (actual time=0.056..0.056 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_7 on tbl_ps_type_7 tbl_ps (cost=0.00..4.36 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_8 on tbl_ps_type_8 tbl_ps (cost=0.00..4.35 rows=1 width=4) (actual time=0.061..0.061 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (interest_type = 1) -> Index Scan using index_b_on_type_9 on tbl_ps_type_9 tbl_ps (cost=0.00..4.36 rows=1 width=4) (actual time=0.067..0.067 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (interest_type = 1) -> Index Scan using index_b_on_type_10 on tbl_ps_type_10 tbl_ps (cost=0.00..3.25 rows=1 width=4) (actual time=0.024..0.024 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_11 on tbl_ps_type_11 tbl_ps (cost=0.00..4.36 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_12 on tbl_ps_type_12 tbl_ps (cost=0.00..4.37 rows=1 width=4) (actual time=0.058..0.058 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_13 on tbl_ps_type_13 tbl_ps (cost=0.00..4.32 rows=1 width=4) (actual time=0.076..0.076 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_14 on tbl_ps_type_14 tbl_ps (cost=0.00..4.34 rows=1 width=4) (actual time=9.745..9.745 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_15 on tbl_ps_type_15 tbl_ps (cost=0.00..4.36 rows=1 width=4) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_16 on tbl_ps_type_16 tbl_ps (cost=0.00..3.14 rows=1 width=4) (actual time=5.258..5.258 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_17 on tbl_ps_type_17 tbl_ps (cost=0.00..3.04 rows=1 width=4) (actual time=6.692..6.692 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_18 on tbl_ps_type_18 tbl_ps (cost=0.00..4.34 rows=1 width=4) (actual time=0.060..0.060 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_19 on tbl_ps_type_19 tbl_ps (cost=0.00..4.34 rows=1 width=4) (actual time=5.287..5.287 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_20 on tbl_ps_type_20 tbl_ps (cost=0.00..4.33 rows=1 width=4) (actual time=5.002..5.002 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_21 on tbl_ps_type_21 tbl_ps (cost=0.00..4.34 rows=1 width=4) (actual time=6.866..6.866 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_22 on tbl_ps_type_22 tbl_ps (cost=0.00..4.35 rows=1 width=4) (actual time=13.451..13.451 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_23 on tbl_ps_type_23 tbl_ps (cost=0.00..4.35 rows=1 width=4) (actual time=7.038..7.038 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_24 on tbl_ps_type_24 tbl_ps (cost=0.00..4.37 rows=1 width=4) (actual time=0.114..0.114 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_25 on tbl_ps_type_25 tbl_ps (cost=0.00..4.37 rows=1 width=4) (actual time=13.667..13.667 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_26 on tbl_ps_type_26 tbl_ps (cost=0.00..4.33 rows=1 width=4) (actual time=0.058..0.058 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_27 on tbl_ps_type_27 tbl_ps (cost=0.00..4.40 rows=1 width=4) (actual time=8.978..8.978 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_28 on tbl_ps_type_28 tbl_ps (cost=0.00..3.87 rows=1 width=4) (actual time=1.496..1.496 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_29 on tbl_ps_type_29 tbl_ps (cost=0.00..3.87 rows=1 width=4) (actual time=4.494..4.494 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) -> Index Scan using index_b_on_type_30 on tbl_ps_type_30 tbl_ps (cost=0.00..3.87 rows=1 width=4) (actual time=1.888..1.888 rows=0 loops=1) Index Cond: ((normalized_text)::text = 'bush'::text) Filter: (type = 1) Total runtime: 101.329 ms (95 rows) db=# SELECT relname,reltuples from pg_class where relname like 'tbl_ps_type%'; relname | reltuples --- ---------------+------------- tbl_ps_type_1 | 1.30524e+06 tbl_ps_type_2 | 889408 tbl_ps_type_3 | 801060 tbl_ps_type_4 | 436223 tbl_ps_type_5 | 213354 tbl_ps_type_6 | 208600 tbl_ps_type_7 | 224911 tbl_ps_type_8 | 232004 tbl_ps_type_9 | 271034 tbl_ps_type_10 | 710783 tbl_ps_type_11 | 382232 tbl_ps_type_12 | 420411 tbl_ps_type_13 | 427860 tbl_ps_type_14 | 353278 tbl_ps_type_15 | 195064 tbl_ps_type_16 | 244756 tbl_ps_type_17 | 1.17223e+06 tbl_ps_type_18 | 194896 tbl_ps_type_19 | 507272 tbl_ps_type_20 | 332233 tbl_ps_type_21 | 184148 tbl_ps_type_22 | 207495 tbl_ps_type_23 | 181174 tbl_ps_type_24 | 54664 tbl_ps_type_25 | 54690 tbl_ps_type_26 | 239964 tbl_ps_type_27 | 920458 tbl_ps_type_28 | 0 tbl_ps_type_29 | 0 tbl_ps_type_30 | 0 (30 rows) In the above example the times are not too bad because bush happens to only be in partition 1, but depending on the query, it's entirely possible that the normalized_text will infact be in 27 of the partitons, which results in some less than stellar performance, and performance we were hoping to gain by moving to partioning and CE. The real problem here is why are the additional 29 partitions even being checked ? In the process of trying to track this down, I discovered that a vacuum analyze on an empty table yeilds stats that are way off reality. However sleeping on it over night I think this is intentional so that the planner has SOME number to work with once the table gets a few rows. Can someone confirm that that is infact what does happen. db=# CREATE table foo(id int4); CREATE TABLE db=# VACUUM ANALYZE foo; VACUUM db=# EXPLAIN ANALYZE select * from foo; QUERY PLAN ------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) (actual time=0.003..0.003 rows=0 loops=1) Total runtime: 0.040 ms (2 rows) -- Darcy Buskermolen Command Prompt, Inc. Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq