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

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

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 |
+----------+-------+---------------+------------+---------+------+------+------------+

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@svana.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.

Stephane Bortzmeyer <bortzmeyer@netaktiv.com> writes:
> 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.

That is the default behavior in the absence of any VACUUM ANALYZE
stats (and your explains look suspiciously like default stats).

If you have stats then the choice will depend on how much of the table
the planner estimates will be scanned.  An indexscan is generally not
a win for scanning more than a few percent of a table.

            regards, tom lane

On Mon, Apr 08, 2002 at 10:15:05AM -0400,
 Tom Lane <tgl@sss.pgh.pa.us> wrote
 a message of 13 lines which said:

> That is the default behavior in the absence of any VACUUM ANALYZE
...
> An indexscan is generally not a win for scanning more than a few
> percent of a table.

You're right, after the VACUUM ANALYZE, both queries use sequential
scan :-)

Stephane Bortzmeyer <bortzmeyer@netaktiv.com> writes:
> You're right, after the VACUUM ANALYZE, both queries use sequential
> scan :-)

Try it with a bigger table ;-).  On a toy-size table the planner doesn't
see any reason to bother with an index at all; the index cannot save any
disk fetches when there's only one or two disk pages in the table anyway
...

            regards, tom lane

On Mon, 2002-04-08 at 07:45, Martijn van Oosterhout wrote:
> "Properly" in your opinion. It's more likely that postgres has a better idea
> of which one is faster...

This seems to be the standard response to any message questioning the
query planner's strategy.

In my opinion, such a response is condescending, discourages user
feedback about postgres performance in real database applications, and
fundamentally misses the point.

The point is that postgres performance frequently sucks on queries that
should be fast.  Any technical explanation about how postgres knows more
than a particular user about its own guts doesn't really bear on that
issue.  People are trying to be helpful by submitting query plans that
look suspicious... but they aren't just EXPLAINing in order to poke
holes in the query planner as entertainment, they are EXPLAINing to try
to figure out why it takes so long to get results from a simple query
that another DBMS can do in a fraction of the time.

You seem to be trying to put an end to the discussion by saying
"postgres knows how to plan queries, so keep your opinions to yourself".
Why is this user even concerned about the way Postgres is executing the
query?  Because he has a reasonable expectation that the query should be
faster than sequential scan of all records, and it's not.  Unless you
can either explain why that expectation is not reasonable, or explain
why postgres fails to meet reasonable expectations, you're just sneering
at a user reporting a problem.  Not good public relations.

Thanks,
Bill Gribble

On 8 Apr 2002, Bill Gribble wrote:

> On Mon, 2002-04-08 at 07:45, Martijn van Oosterhout wrote:
> > "Properly" in your opinion. It's more likely that postgres has a better idea
> > of which one is faster...
>
> This seems to be the standard response to any message questioning the
> query planner's strategy.

Perhaps you missed the part of his message where he asks for more
information and gives a short reason why it *might* be choosing
sequence scan?

(quoted from Martijn's message)
"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."