Обсуждение: why does explain tell me I'm using a seq scan?

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)

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