misbehaving planer?

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/