misbehaving planer? - Mailing list pgsql-hackers

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.3on 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)        ->
IndexScan 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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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_9on 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_10on 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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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
ontbl_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+06tbl_ps_type_2  |      889408tbl_ps_type_3  |
801060tbl_ps_type_4 |      436223tbl_ps_type_5  |      213354tbl_ps_type_6  |      208600tbl_ps_type_7  |
224911tbl_ps_type_8 |      232004tbl_ps_type_9  |      271034tbl_ps_type_10 |      710783tbl_ps_type_11 |
382232tbl_ps_type_12|      420411tbl_ps_type_13 |      427860tbl_ps_type_14 |      353278tbl_ps_type_15 |
195064tbl_ps_type_16|      244756tbl_ps_type_17 | 1.17223e+06tbl_ps_type_18 |      194896tbl_ps_type_19 |
507272tbl_ps_type_20|      332233tbl_ps_type_21 |      184148tbl_ps_type_22 |      207495tbl_ps_type_23 |
181174tbl_ps_type_24|       54664tbl_ps_type_25 |       54690tbl_ps_type_26 |      239964tbl_ps_type_27 |
920458tbl_ps_type_28|           0tbl_ps_type_29 |           0tbl_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.40rows=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/