Re: Why sequential scan when there's a supporting index?

On Fri, 2002-05-24 at 08:25, Henshall, Stuart - WCP wrote:
> Try:
> SELECT tx_date FROM t_lane_tx ORDER BY tx_date DESC LIMIT 1;
> hth,
> - Stuart

Thanks, Stuart and John.

Is this a bug, or a "feature"?  I ask, because in other
RDBMS', this is absolutely supported by indexes:
   SELECT MAX(tx_date) FROM t_lane_tx;

> > -----Original Message-----
> > From: Ron Johnson [mailto:ron.l.johnson@cox.net]
> >
> > Hi,
> >
> > As you can see, I "VACUUM VERBOSE ANALYZE" my table, describe
> > the index on the table, then try to find the max() value of
> > the indexed field.  However, EXPLAIN still shows that the
> > query wants to sequentially scan the table.
> >
> > Why?
> >
> > TIA,
> > Ron
> >
> > test2=# vacuum verbose analyze t_lane_tx;
> > NOTICE:  --Relation t_lane_tx--
> > NOTICE:  Pages 1785858: Changed 0, Empty 0; Tup 33931294: Vac
> > 0, Keep 0,
> > UnUsed 0.
> >     Total CPU 77.03s/7.24u sec elapsed 494.00 sec.
> > NOTICE:  Analyzing t_lane_tx
> > VACUUM
> > test2=# \d i_lane_tx_tmp
> > Index "i_lane_tx_tmp"
> >  Column  | Type
> > ---------+------
> >  tx_date | date
> > btree
> >
> > test2=# explain select max(tx_date) from t_lane_tx;
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=2209999.20..2209999.20 rows=1 width=4)
> >   ->  Seq Scan on t_lane_tx  (cost=0.00..2125170.96 rows=33931296
> > width=4)
> >
--
+---------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net     |
| Jefferson, LA  USA      http://ronandheather.dhs.org:81 |
|                                                         |
| "I have created a government of whirled peas..."        |
|   Maharishi Mahesh Yogi, 12-May-2002,                   |
!   CNN, Larry King Live                                  |
+---------------------------------------------------------+