Client is seeing continual performance degradation on
updates and queries from a large database. Any help
Client is using PostgreSQL 7.4.2 on Sparcv9 650MHZ
cpu, 2GB Ram, running Solaris.
We have the following tables:
evt_id bigserial, unique
obj_id numeric(6), unique
name varchar(22), unique
Update processes run continually throughout the day in
which rows are inserted but none deleted. The
EVENT_TBL is currently very big, w/ over 5 million
rows. The CONTROL_TBL is fairly small w/ around 4000
rows. We're doing a "VACUUM ANALYZE" on each table
after each update has been completed and changes
committed. Each night we drop all the indexes and
Do I understand correctly, however, that when you
create a unique SERIAL column an index is
automatically created on that column? If so, does
that sound like a possible culprit? We are not doing
any reindexing on that index at all. Could it be
suffering from index bloat? Do we need to
periodically explicity run the command:
reindex index event_tbl_evt_id_key;
Even seemingly simple commands are taking forever.
select evt_id from event_tbl where evt_id=1;
takes over a minute to complete.
Here is a slightly more complicated example along with
its explain output:
select events.evt_id, ctrl.name, events.d1,
events.val, events.d2, events.correction, ctrl.type,
ctrl.freq from event_tbl events, control_tbl ctrl
where events.obj_id = ctrl.obj_id and events.evt_id >
3690000 order by events.evt_id limit 2000;
Limit (cost=0.00..6248.56 rows=2000 width=118)
-> Nested Loop (cost=0.00..7540780.32
-> Index Scan using event_tbl_evt_id_key on
event_tbl events (cost=0.00..237208.57 rows=2413606
Filter: (evt_id > 3690000)
-> Index Scan using control_tbl_obj_id_idx
on control_tbl ctrl (cost=0.00..3.01 rows=1 width=75)
Index Cond: ("outer".obj_id =
This takes minutes to return 2000 rows.
Thank you in advance.
Do you Yahoo!?
All your favorites on one personal page Try My Yahoo!
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2005-01-31 17:46:19|
|Subject: Re: Performance degredation at client site |
|Previous:||From: Marty Scholes||Date: 2005-01-31 15:24:55|
|Subject: Re: PostgreSQL clustering VS MySQL clustering|