Subject: Re: Sequential scan where Index scan expected (update)
From: Ragnar <gnari@hive.is>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4407F031.6050706@obviously.com>
References: <244004377.20060302124132@isb.com.pl>
	<2873.1141312882@sss.pgh.pa.us>  <4407F031.6050706@obviously.com>
Content-Type: text/plain
Date: Fri, 03 Mar 2006 09:31:37 +0000
Message-Id: <1141378297.18656.119.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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