By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.