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