Re: Doesn't use index, why?

Marco Catunda <catunda(at)pobox(dot)com> writes:
> naslog=# explain select * from desconexao where time < '2000-12-10';
> NOTICE: QUERY PLAN:

> Seq Scan on desconexao (cost=0.00..19547.71 rows=231489 width=103)

> naslog=# explain select * from desconexao where time >=
> '2000-12-10';
> NOTICE: QUERY PLAN:

> Seq Scan on desconexao (cost=0.00..19547.71 rows=427128 width=103)

> The number of records are:

> naslog=# select count(*) from desconexao where time >= '2000-12-10';
> count
> --------
> 585789
> (1 row)

> naslog=# select count(*) from desconexao where time < '2000-12-10';
> count
> -------
> 72828
> (1 row)

In this case the planner is doing *exactly* the right thing; it is
smarter than you are. If you want to prove it, force the planner to
use an indexscan by doing SET ENABLE_SEQSCAN TO OFF. Then time the
query, and compare the runtime against the seqscan version.

The bottom line here is that a query that needs to touch more than a
few percent of the rows in a table is better off being done as a
seqscan.

regards, tom lane