Re: Stupid database use the index!

> Why is this?
>
> web=# explain select account_nbr from apprsl_accounts where account_nbr =
> 3315;
> NOTICE: QUERY PLAN:
>
> Seq Scan on apprsl_accounts (cost=0.00..10317.24 rows=1 width=8)
>
> EXPLAIN
> web=# explain select account_nbr from apprsl_accounts where account_nbr =
> '3315';
> NOTICE: QUERY PLAN:
>
> Index Scan using apprsl_accounts_account_nbr on apprsl_accounts
> (cost=0.00..4.80 rows=1 width=8)
>
> EXPLAIN

I suspect there is a type conversion issue here. I believe 3315 will
get converted implicitly to an int4 (in the parser). If the type of the
field isn't an int4, then the database has to use a sequential scan
because the indexes are built using non-int4 comparitive functions.
Meanwhile, the quoted version gets automatically type-cast
appropriately after parsing and can then use the index.

Hope that helps,

Mike Mascari