Re: Why does it not use the index?

On Mon, 21 Jul 2003, Philip Greer wrote:

> dumps=# \d fal_profdel
>                Table "fal_profdel"
>  Attribute |           Type           | Modifier
> -----------+--------------------------+----------
>  sid       | character(4)             | not null
>  card_num  | character(19)            | not null
>  date_del  | timestamp with time zone |
>  filename  | character varying(30)    |
> Indices: fal_prfdel_cn,
>          fal_prfdel_date,
>          fal_prfdel_pk
>
> dumps=# \d fal_prfdel_cn
>    Index "fal_prfdel_cn"
>  Attribute |     Type
> -----------+---------------
>  card_num  | character(19)
> unique btree
>
> dumps=# explain select card_num from fal_profdel where card_num = 'removed_for_privacy';
> NOTICE:  QUERY PLAN:
>
> Seq Scan on fal_profdel  (cost=0.00..120546.39 rows=46649 width=12)
>
> EXPLAIN
> ================================================================================
>
> Now, why the heck is the select query not using the index? I've tried
> it by having an exact 19 character card_num as well - still explains
> as a 'Seq Scan' (tablespace scan) - and each query takes up to 37
> seconds (thus confirming that it is indeed doing scans and not using
> the index).

Have you vacuum analyzed the table recently? What does explain show if you
do set enable_seqscan=off; before the explain and then how long does the
query actually take to run with seqscan disabled.