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