Re: Seq. scan when using comparison operators, why? [netaktiv.com #150]

On Mon, Apr 08, 2002 at 02:37:06PM +0200, Stephane Bortzmeyer wrote:
> I have an index on column "numero". When, I use a WHERE numero=8,
> PostgreSQL uses an index scan (OK) but no when using comparison
> operators like numero>8.
>
> essais=# explain select * from pourspip where numero>8;
> NOTICE: QUERY PLAN:
>
> Seq Scan on pourspip (cost=0.00..22.50 rows=333 width=28)
>
> EXPLAIN
> essais=# explain select * from pourspip where numero=8;
> NOTICE: QUERY PLAN:
>
> Index Scan using numero_idx on pourspip (cost=0.00..8.14 rows=10 width=28)
>
> EXPLAIN

How many rows are there in the table? If you're going to match most of the
table, it's faster to scan the entire table than it is the scan the index.

> Why? MySQL seems able to do it "proprely":
>
> mysql> explain select * from pourspip where numero>8;
> +----------+-------+---------------+------------+---------+------+------+------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----------+-------+---------------+------------+---------+------+------+------------+
> | pourspip | range | numero_idx | numero_idx | 5 | NULL | 2 | where used |
> +----------+-------+---------------+------------+---------+------+------+------------+

"Properly" in your opinion. It's more likely that postgres has a better idea
of which one is faster...

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.