Re: [GENERAL] why does explain tell me I'm using a seq scan?

[Mark Harrison]

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

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

[Mark Harrison]

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

Re: [GENERAL] why does explain tell me I'm using a seq scan?

[Rod Taylor]

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