Skip site navigation (1) Skip section navigation (2)

Why sequential scan when there's a supporting index?

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Why sequential scan when there's a supporting index?
Date: 2002-05-24 13:14:40
Message-ID: 1022246084.631.26.camel@rebel (view raw or flat)
Thread:
Lists: pgsql-novice
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                                  |
+---------------------------------------------------------+


Responses

pgsql-novice by date

Next:From: Henshall, Stuart - WCPDate: 2002-05-24 13:25:27
Subject: Re: Why sequential scan when there's a supporting index?
Previous:From: Ron JohnsonDate: 2002-05-24 13:02:03
Subject: Re: script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group