From: | "P(dot)J(dot) \"Josh\" Rovero" <rovero(at)sonalysts(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Glacial delete |
Date: | 2001-08-18 15:49:58 |
Message-ID: | 3B7E8EA6.3040005@sonalysts.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Have a pretty simple table with about 420K rows:
Table "wx_grib_file"
Attribute | Type | Modifier
--------------+--------------------------+----------
grib_file_id | oid | not null
name | character(40) |
parse_time | timestamp with time zone |
Index: wx_grib_file_pkey
In psql, trying to delete a number of rows is extremely slow. Aggregates
(count, min, max, etc) run in a couple of seconds. But a delete
using a range of grib_file_id or parse_time takes about a second
per row, which is painful for deletes of thousands of records.
Here are the results of an example vacuum done after the delete:
# vacuum verbose analyze wx_grib_file;
NOTICE: --Relation wx_grib_file--
NOTICE: Pages 5200: Changed 9, reaped 29, Empty 0, New 0; Tup 455033:
Vac 2500, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 88, MaxLen 88;
Re-using: Free/Avail. Space 222204/222204; EndEmpty/Avail. Pages 0/29.
CPU 0.86s/0.29u sec.
NOTICE: Index wx_grib_file_pkey: Pages 2448; Tuples 455033: Deleted
2500. CPU 0.39s/3.65u sec.
NOTICE: Rel wx_grib_file: Pages: 5200 --> 5171; Tuple(s) moved: 2500.
CPU 0.03s/0.46u sec.
NOTICE: Index wx_grib_file_pkey: Pages 2449; Tuples 455033: Deleted
2500. CPU 0.29s/3.31u sec.
NOTICE: Analyzing...
VACUUM
Any suggestions on how to speed this up?
--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: rovero(at)sonalysts(dot)com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-08-18 17:52:25 | Re: Glacial delete |
Previous Message | Martín Marqués | 2001-08-18 14:47:00 | Re: Installing Postrgesql 7.1.2-3 |