why does explain tell me I'm using a seq scan?

On Fri, 2003-11-07 at 19:11, Mark Harrison wrote:

I have indexed two columns in a table. Can somebody explain to me why
the first query below uses an Index Scan while the second uses a Seq
Scan?

The first expects to find a single row, the second expects to find 17000
rows, a significant portion of the table where an index scan would be a
loss based on current tuning parameters.

--
Rod Taylor <pg [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Nov 7, 2003, at 5:11 PM, Mark Harrison wrote:

I have indexed two columns in a table. Can somebody explain to me why
the first query below uses an Index Scan while the second uses a Seq
Scan?

Many TIA!
Mark

planb=# \d abcs
Table "public.abcs"
Column   |          Type          |                   Modifiers
-----------+------------------------ 
+-----------------------------------------------
abcid   | integer                | not null default  
nextval('abcid_seq'::text)
type      | character varying(255) |
versionof | integer                |
Indexes: abcs_pkey primary key btree (abcid),
abcs_versionof btree (versionof)

planb=# explain select type from abcs where abcid = 6339;
QUERY PLAN
-----------------------------------------------------------------------
-----
Index Scan using abcs_pkey on abcs (cost=0.00..6.01 rows=1 width=145)
Index Cond: (abcid = 6339)
(2 rows)

planb=# explain select type from abcs where versionof = 6339;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on abcs (cost=0.00..59182.10 rows=16137 width=145)
Filter: (versionof = 6339)
(2 rows)

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

The first query is using a unique index while the second is not.

The first query is guaranteed to only return a single row, so it
doesn't take many records before the index is faster.

The optimizer is looking at the statistics and figuring that the second
query could be done better with a sequential scan (perhaps there aren't
many rows). Have you analyzed the table to get the statistics up to
date? It could also be that the seq scan is faster with the size of
your table.

Adam Ruth

Adam Ruth wrote:

The optimizer is looking at the statistics and figuring that the second
query could be done better with a sequential scan (perhaps there aren't
many rows). Have you analyzed the table to get the statistics up to
date? It could also be that the seq scan is faster with the size of
your table.

Thanks! An analyze fixed it and reduced our search time from seconds
to milliseconds.

Is there ever any reason to do a "vacuum" without doing a "vacuum analyze"?

Mark