Glacial delete

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
***********************************************************************

Responses

Browse pgsql-general by date

  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