Re: index scan on =, but not < ?

On Tue, 8 Mar 2005, Rick Schumeyer wrote:

> =# explain select * from data where x = 0;
> -------------------------------------------------------------------------
> Index Scan using data_x_ix on data (cost=0.00..78.25 rows=19 width=34)
> Index Cond: (x = 0::double precision)
>
> But this command, in which the only difference if > instead of =, is a
> sequential scan.
>
> =# explain select * from data where x > 0;
> ------------------------------------------------------------------
> Seq Scan on data (cost=0.00..1722605.20 rows=62350411 width=34)
> Filter: (x > 0::double precision)
>
> Why is this?

That is because it's faster to execute the x>0 query with a seq. scan then
a index scan. Postgresql is doing the right thing here.

Pg estimates that the first query will return 19 rows and that the second
query will return 62350411 rows. To return 62350411 rows it's faster to
just scan the table and not use the index.

--
/Dennis Björklund