Date: Fri, 3 Mar 2006 02:46:59 -0700
From: Michael Fuhr <mike@fuhr.org>
To: "Gregory S. Williamson" <gsw@globexplorer.com>
Cc: Bryce Nesbitt <bryce1@obviously.com>, pgsql-sql@postgresql.org
Subject: Re: Sequential scan where Index scan expected (update)
Message-ID: <20060303094659.GA36052@winnie.fuhr.org>
References: 
 <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net>

On Thu, Mar 02, 2006 at 11:37:53PM -0800, Gregory S. Williamson wrote:
> It seems unlikely but maybe try an explict cast for the thing_id call, e.g.
> explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000::bigint;

The server is pre-8.0 so it's likely that this is indeed the problem.
Unfortunately the table that was shown in the \d output (xx_thing)
isn't the same table as in the update command (xx_thing_event) so
we can only guess that thing_event_id is a bigint based on what
xx_thing looks like.

> It may also be that 5842 rows is enough that the planner decides
> it is faster to do a sequential scan that the busier index scan
> (read index, get data row, versus just reading all the necessary
> pages in one faster sequential scan).
[...]
>                              QUERY PLAN
> ---------------------------------------------------------------------
>  Seq Scan on xx_thing_event  (cost=0.00..5842.48 rows=1 width=110)
>    Filter: (thing_event_id = 10000)
> (2 rows)

The 5842.48 figure isn't a row count, it's a cost estimate that
includes the number of pages and rows in the file.  The row count
estimate is 1 row; if the cost estimate for a sequential scan is
5842.48 then an index scan to fetch one row would surely be faster.

If thing_event_id is a bigint and has an index then casting the
value to bigint should result in an index scan.  Another way would
be to quote the value:

UPDATE xx_thing_event SET thing_color = 'foo'
  WHERE thing_event_id = '10000';

-- 
Michael Fuhr