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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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 |
+---------------------------------------------------------+