Re: Doesn't use index, why?

Marco Catunda wrote:

> Hello,
>
> I have some doubt about index.
> Look the follow example:
>
>
>     naslog=# \d desconexao
>                                    Table "desconexao"
>        Attribute    |    Type     |                    Modifier
>     ----------------+-------------+-------------------------------------------------
>      id             | integer     | not null default
>     nextval('id_desconexao'::text)
>      time           | timestamp   |
>      client_user    | varchar(20) |
>      client         | varchar(40) |
>      ip_nas         | inet        |
>      ip_client_user | inet        |
>      disconnect     | smallint    |
>      user_time      | interval    |
>      data_rate      | integer     |
>      called_number  | varchar(14) |
>      calling_number | varchar(14) |
>      filtrado       | boolean     | default 'f'::bool
>     Indices: client_desconexao_idx,
>              desconexao_pkey,
>              filtro_idx,
>              time_idx
>
>
>     naslog=# \d time_idx
>        Index "time_idx"
>      Attribute |   Type
>     -----------+-----------
>      time      | timestamp
>     btree
>
>
>     naslog=# explain select * from desconexao where time = '2000-12-01';
>     NOTICE:  QUERY PLAN:
>
>     Index Scan using time_idx on desconexao  (cost=0.00..20.18 rows=5
>     width=103)
>
>     EXPLAIN
>
>
>
> Every thing is perfect, but when i execute de follow query:
>
>
>     naslog=# explain select * from desconexao where time > '2000-12-01';
>     NOTICE:  QUERY PLAN:
>
>     Seq Scan on desconexao  (cost=0.00..19547.71 rows=657958 width=103)
>
>     EXPLAIN
>
>
> Why postgresql didn't use the time_idx index? I only changed the
> operator
> "=" to ">" in clause "where".
>
>
> Thank you.
> Regards
> -- Marco Catunda
>
>
Firstly you should include your version along with the report.

Two thoughts ... did you run vacuum?
Of the total number of records how many are greater than 2000-12-01? It
could be that postgresql figures that a large portion of the result set
is greater than this date and it is faster to do a sequential scan