Re: Sequential scan where Index scan expected (update)

On fim, 2006-03-02 at 23:28 -0800, Bryce Nesbitt wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys.  This is an older postgres.
> Can anyone help figure out why?
> 
> 
> demo=# \d xx_thing
>                      Table "public.xx_thing"
>          Column          |            Type             | Modifiers
> -------------------------+-----------------------------+-----------
>  thing_id              | bigint                      | not null
...
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
>                              QUERY PLAN
> ---------------------------------------------------------------------
>  Seq Scan on xx_thing_event  (cost=0.00..5842.48 rows=1 width=110)
...
> demo=# select * from version();
>                                                  version
> ----------------------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3

try one of: ... WHERE thing_event_id=10000::bigint ... WHERE thing_event_id='10000'

or upgrade to >= 8.0

your problem is that the 10000 in 'thing_event_id=10000' is parsed as an
integer but not a bigint, so the planner
does not find a matching index. so you either have to
scpecify ::bigint or ::int8 in the query or quote the number, which will
cause postgres to cast it to the 
column's type.

if you try  ... WHERE thing_event_id=10000000000
you will see the index used because this number cannot 
be a int4 so is parsed as a bigint.

newer versions of Postgresql can deal with this.

gnari