From: Gregory S. Williamson <gsw@globexplorer.com>
To: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: Sequential scan where Index scan expected (update)
Date: Thu, 2 Mar 2006 23:37:53 -0800
Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net> (raw)
Perhaps it is clogged with dead tuples -- has it been vacuumed recently with enough FSM space ?
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;
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).
If you set the sequential scan parameter in the config file and reload postgres does the same query get faster ? (not suggesting this for real runtime use but it can be useful to diagnose issues).
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Bryce Nesbitt
Sent: Thu 3/2/2006 11:28 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: [SQL] Sequential scan where Index scan expected (update)
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
thing_model | character varying(128) |
thing_color | character varying(128) |
thing_year | integer |
Indexes:
"xx_thing_pkey" primary key, btree (thing_id)
demo=# analyze verbose xx_thing_event;
INFO: analyzing "public.xx_thing_event"
INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows
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)
Filter: (thing_event_id = 10000)
(2 rows)
demo=# select * from version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
!DSPAM:4407f048253644359117518!
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: gsw@globexplorer.com, bryce1@obviously.com
Subject: Re: Sequential scan where Index scan expected (update)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net>
References: <71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net>
* 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