Re: Sequential scan where Index scan expected (update) - Mailing list pgsql-sql

From Michael Fuhr
Subject Re: Sequential scan where Index scan expected (update)
Date
Msg-id 20060303094659.GA36052@winnie.fuhr.org
Whole thread Raw
In response to Re: Sequential scan where Index scan expected (update)  ("Gregory S. Williamson" <gsw@globexplorer.com>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Ragnar
Date:
Subject: Re: Sequential scan where Index scan expected (update)
Next
From: Ragnar
Date:
Subject: Re: Why do I get these results?????