Re: index use again and again

The reason i believe is that you are doing comparisions instead of direct
this is the value so the table would have to do a sequencial scan of the
table

Darren

On Tue, 12 Feb 2002, Holger Marzen wrote:

> I read the Postgres boog, browsed the mailing list archives and have
> still no clue.
>
> I have a table with about 150.000 rows and put some indexes (not unique)
> on it. If I use "=" in the where clause tha index is used, if I use ">"
> or "between" then it is not used. It happens both with 7.1.3 and the new
> 7.2:
>
> |db1=# select count(*) from verfuegbarkeiten;
> | count
> |--------
> | 152428
> |(1 row)
> |
> |db1=# explain select red from verfuegbarkeiten where datum =
> |'2002-01-01';
> |NOTICE:  QUERY PLAN:
> |
> |Index Scan using verfuegbarkeiten_datum_idx on verfuegbarkeiten
> |(cost=0.00..1489.67 rows=566 width=4)
> |
> |EXPLAIN
> |db1=# explain select red from verfuegbarkeiten where datum >
> |'2002-01-01';
> |NOTICE:  QUERY PLAN:
> |
> |Seq Scan on verfuegbarkeiten  (cost=0.00..3820.35 rows=22322 width=4)
> |
> |EXPLAIN
>
> "vacuum analyze" has be done before (and is done daily). CASTs like
> "where datum > '2002-01-01'::date" don't help.
>
> Are 152428 rows not enough to use the index?
>
>
> --
> PGP/GPG Key-ID:
> http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>