Обсуждение: Delete performance

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

"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