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
