From: Michael Fuhr <mike@fuhr.org>
To: Gregory S. Williamson <gsw@globexplorer.com>
Cc: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequential scan where Index scan expected (update)
Date: Fri, 3 Mar 2006 02:46:59 -0700
Message-ID: <20060303094659.GA36052@winnie.fuhr.org> (raw)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, gsw@globexplorer.com, bryce1@obviously.com
Subject: Re: Sequential scan where Index scan expected (update)
In-Reply-To: <20060303094659.GA36052@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox