Thread: 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.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/

> db=#  \d s_ps.tbl_ps_type_1
>                                  Table "s_ps.tbl_ps_type_1"
...
> Check constraints:
>     "tbl_ps_typ_1_type_check" CHECK (type = 1)
> Inherits: tbl_ps
...
> myyearbook=# EXPLAIN ANALYZE SELECT uid FROM tbl_ps WHERE
> type = 1 and
> normalized_text='bush';
>
>
>
> QUERY PLAN
...
>          ->  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)

Do you have corresponding constraints on all other table partitions ?

btw. I doubt that the many partial indexes are really helpful here.
What you are doing basically only replaces one btree header page.

Andreas

Darcy Buskermolen <darcyb@commandprompt.com> writes:
> 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.

Do you have constraint_exclusion turned on?  What are the check
constraints on the other children of tbl_ps?  This example doesn't
really show whether the planner is misbehaving or not.

The multiple-partial-index setup on tbl_ps_type_1 looks pretty silly to
me...  it seems unlikely to buy anything except extra planning overhead.
        regards, tom lane

On Friday 20 October 2006 08:26, Tom Lane wrote:
> Darcy Buskermolen <darcyb@commandprompt.com> writes:
> > 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.
>
> Do you have constraint_exclusion turned on?  What are the check
> constraints on the other children of tbl_ps?  

Yes CE is on (you can see it in the session paste). The other child tables 
have simular CHECK's of type=2, type=3, type=4 and so on.. 1 for each of the 
30 tables.


> This example doesn't 
> really show whether the planner is misbehaving or not.
>
> The multiple-partial-index setup on tbl_ps_type_1 looks pretty silly to
> me...  it seems unlikely to buy anything except extra planning overhead.

This was a direct port from a big fat table. I agree, I'm not convinced that 
the  partial indexes will buy me much, but this box is so IO bound that the 
planner overhead my just offset the needing to IO bigger indexes.


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Darcy Buskermolen <darcyb@commandprompt.com> writes:
> Yes CE is on (you can see it in the session paste). The other child tables 
> have simular CHECK's of type=2, type=3, type=4 and so on.. 1 for each of the 
> 30 tables.

[ looks again... ]  Oh, here's your problem:
type           | smallint              | 

Check constraints:   "tbl_ps_typ_1_type_check" CHECK (type = 1)

That CHECK is a cross-type comparison (int2 vs int4).  Per the docs:
    Avoid cross-datatype comparisons in the CHECK constraints, as the    planner will currently fail to prove such
conditionsfalse. For    example, the following constraint will work if x is an integer    column, but not if x is a
bigint:
    CHECK ( x = 1 )
    For a bigint column we must use a constraint like: 
    CHECK ( x = 1::bigint )
    The problem is not limited to the bigint data type --- it can    occur whenever the default data type of the
constantdoes not match    the data type of the column to which it is being    compared. Cross-datatype comparisons in
thesupplied queries are    usually OK, just not in the CHECK conditions.
 

So you can either cast to int2 in the CHECKs, or change the column to
plain integer (int2 is probably not saving you anything here anyway).

>> The multiple-partial-index setup on tbl_ps_type_1 looks pretty silly to
>> me...  it seems unlikely to buy anything except extra planning overhead.

> This was a direct port from a big fat table. I agree, I'm not convinced that 
> the  partial indexes will buy me much, but this box is so IO bound that the 
> planner overhead my just offset the needing to IO bigger indexes.

Well, you should measure it, but I bet the planner wastes way more time
considering the twenty-some indexes than is saved by avoiding one level
of btree search, which is about the most you could hope for.
        regards, tom lane

On Friday 20 October 2006 09:27, Tom Lane wrote:
> Darcy Buskermolen <darcyb@commandprompt.com> writes:
> > Yes CE is on (you can see it in the session paste). The other child
> > tables have simular CHECK's of type=2, type=3, type=4 and so on.. 1 for
> > each of the 30 tables.
>
> [ looks again... ]  Oh, here's your problem:
>
>  type           | smallint              |
>
> Check constraints:
>     "tbl_ps_typ_1_type_check" CHECK (type = 1)
>
> That CHECK is a cross-type comparison (int2 vs int4).  Per the docs:

Dohh, thanks for the sanity check. I compleatly missed that.
>
>      Avoid cross-datatype comparisons in the CHECK constraints, as the
>      planner will currently fail to prove such conditions false. For
>      example, the following constraint will work if x is an integer
>      column, but not if x is a bigint:
>
>      CHECK ( x = 1 )
>
>      For a bigint column we must use a constraint like:
>
>      CHECK ( x = 1::bigint )
>
>      The problem is not limited to the bigint data type --- it can
>      occur whenever the default data type of the constant does not match
>      the data type of the column to which it is being
>      compared. Cross-datatype comparisons in the supplied queries are
>      usually OK, just not in the CHECK conditions.
>
> So you can either cast to int2 in the CHECKs, or change the column to
> plain integer (int2 is probably not saving you anything here anyway).
>
> >> The multiple-partial-index setup on tbl_ps_type_1 looks pretty silly to
> >> me...  it seems unlikely to buy anything except extra planning overhead.
> >
> > This was a direct port from a big fat table. I agree, I'm not convinced
> > that the  partial indexes will buy me much, but this box is so IO bound
> > that the planner overhead my just offset the needing to IO bigger
> > indexes.
>
> Well, you should measure it, but I bet the planner wastes way more time
> considering the twenty-some indexes than is saved by avoiding one level
> of btree search, which is about the most you could hope for.

Yes mesurement will happen, step one was the partioning.

>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Fri, 2006-10-20 at 12:27 -0400, Tom Lane wrote:
> > This was a direct port from a big fat table. I agree, I'm not
> convinced that 
> > the  partial indexes will buy me much, but this box is so IO bound
> that the 
> > planner overhead my just offset the needing to IO bigger indexes.
> 
> Well, you should measure it, but I bet the planner wastes way more
> time
> considering the twenty-some indexes than is saved by avoiding one
> level
> of btree search, which is about the most you could hope for.

I note that in allpaths.c:set_plain_rel_pathlist() we consider partial
indexes before we consider constraint exclusion. We normally wouldn't
notice that but, in this case, that would be a big loss.

Is there a reason for that? check_partial_indexes() doesn't seem to have
important side-effects that are required for testing whether
relation_excluded_by_constraints()

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com