Re: Sequential scan where Index scan expected (update)
От | Gregory S. Williamson |
---|---|
Тема | Re: Sequential scan where Index scan expected (update) |
Дата | |
Msg-id | 71E37EF6B7DCC1499CEA0316A2568328024BC055@loki.wc.globexplorer.net обсуждение исходный текст |
Ответ на | Sequential scan where Index scan expected (update) (Bryce Nesbitt <bryce1@obviously.com>) |
Ответы |
Re: Sequential scan where Index scan expected (update)
(Michael Fuhr <mike@fuhr.org>)
|
Список | pgsql-sql |
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 indexscan (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 suggestingthis 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=1width=110) Filter: (thing_event_id = 10000) (2 rows) demo=# select * from version(); version ----------------------------------------------------------------------------------------------------------PostgreSQL 7.4.1on 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.orgso that your message can get through to the mailing list cleanly !DSPAM:4407f048253644359117518!
В списке pgsql-sql по дате отправления: