Обсуждение: Index Using

hello,

I have in db table with 3 columns... table name is l_model_to_part
columns: i_model_id, i_part_id, i_year

I have index on every column separately and primary key is (i_model_id,
i_part_id, i_year)

when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE i_part_id=234;
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Index Scan using index_20 on l_model_to_part  (cost=0.00..3.37 rows=10
width=12)
    Index Cond: (i_part_id = 234)


when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE i_model_id=234;
                              QUERY PLAN
---------------------------------------------------------------------
  Seq Scan on l_model_to_part  (cost=0.00..1400.59 rows=866 width=12)
    Filter: (i_model_id = 234)

but, when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE
i_model_id=234 AND i_model_id=456;
                                     QUERY PLAN
-----------------------------------------------------------------------------------
  Index Scan using index_50 on l_model_to_part  (cost=0.00..41.84
rows=11 width=12)
    Index Cond: ((i_model_id = 234) AND (i_model_id = 456))

my question is, why postgres doesn't use index_50 in second query???
when I create this structure in clear db, everything is all right...

thanx, hlavki


--

[ miso hlavac ][ hlavki@medium13.sk ][ http://hlavki.sk ]
[ icq:94900232 ][ callto://hlavki ]

Michal Hlavac wrote:

 > my question is, why postgres doesn't use index_50 in second query???
 > when I create this structure in clear db, everything is all right...
 >

of course, index_50 is BTREE index with one column (i_model_id)...

thanx, hlk

--

[ miso hlavac ][ hlavki@medium13.sk ][ http://hlavki.sk ]
[ icq:94900232 ][ callto://hlavki ]

On Mon, 19 Jul 2004, Michal Hlavac wrote:

> hello,
>
> I have in db table with 3 columns... table name is l_model_to_part
> columns: i_model_id, i_part_id, i_year
>
> I have index on every column separately and primary key is (i_model_id,
> i_part_id, i_year)
>
> when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE i_part_id=234;
>                                      QUERY PLAN
> ----------------------------------------------------------------------------------
>   Index Scan using index_20 on l_model_to_part  (cost=0.00..3.37 rows=10
> width=12)
>     Index Cond: (i_part_id = 234)
>
>
> when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE i_model_id=234;
>                               QUERY PLAN
> ---------------------------------------------------------------------
>   Seq Scan on l_model_to_part  (cost=0.00..1400.59 rows=866 width=12)
>     Filter: (i_model_id = 234)
>
> but, when I use: EXPLAIN SELECT * FROM l_model_to_part WHERE
> i_model_id=234 AND i_model_id=456;
>                                      QUERY PLAN
> -----------------------------------------------------------------------------------
>   Index Scan using index_50 on l_model_to_part  (cost=0.00..41.84
> rows=11 width=12)
>     Index Cond: ((i_model_id = 234) AND (i_model_id = 456))
>
> my question is, why postgres doesn't use index_50 in second query???

How many rows are there in the table?  It looks like it's probably simply
guessing that the 866 estimated rows is high enough to make the index scan
more expensive.

So the questions would be:
 Is 866 a reasonable estimate of the number of rows with i_model_id=234?
 What does explain analyze say for the second query with and without
  set enable_seqscan=off?