Delete performance

Started by P.J. "Josh" Roveroover 24 years ago2 messagesgeneral
Jump to latest
#1P.J. "Josh" Rovero
rovero@sonalysts.com

Is there a performance issue deleting rows of a table where one
field is toasted text?

I have a database with two different tables, and delete both based
on timestamp criteria. One is lightning fast, even for
thousands of rows, the other is very slow.

The first table has a timestamp criteria that is discrete, i.e.,
there are large number of rows with the same timestamp. This
table has no toasted fields. This deletes very quickly.

The second table has a timestamp value that is more of a
continuous function (only a few rows will share the same
second of time). A single field is toasted, and contains
2K to 15K characters. This deletes very slowly, like only
a couple of records a second. The table structure is pretty
simple:

Attribute | Type | Modifier
------------+--------------------------+----------
name | text | not null
parse_time | timestamp with time zone | not null
data | text |

Any hints as to what's going on? Postgresql 7.1.3,
HP-UX 10.20........

--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: P.J. "Josh" Rovero (#1)
Re: Delete performance

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

Is there a performance issue deleting rows of a table where one
field is toasted text?

It'd require an additional table access for each such field, but
that seems unlikely to explain the performance problem you're seeing.
Have you looked at EXPLAIN output for the two cases?

regards, tom lane