Re: Doesn't use index, why?

Marco Catunda <catunda@pobox.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