Re: Query planner isn't using my indices

"Alaric B. Snell" <abs@frontwire.com> writes:

> To cut a long story short, my largish development database was running the
> query I was tinkering with very slowly.
>
> Looking a little deeper, I found that it was always doing a full table
> scan.
>
> Which is odd, seeing as we're selecting on a uniquely indexed field...
>
> frontwire=# \d stakeholder_pk
> Index "stakeholder_pk"
>  Attribute |  Type
> -----------+--------
>  id        | bigint
> unique btree
>
> frontwire=# explain select * from stakeholder where id = 1;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on stakeholder  (cost=0.00..602.81 rows=1 width=336)

The query planner isn't *quite* smart enough currently to realize that
it can use the index in this case, because an unadorned "1" is an int
(not bigint) constant.  Try:

EXPLAIN SELECT * FROM stakeholder WHERE id = 1::bigint;

or

EXPLAIN SELECT * FROM stakeholder WHERE id = '1';

[I think either will work]

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863