Re: DELETE performance issues

Hey,

I've gone and increased shared memory and now the deletes seem to be
really fast. I guess it had to do with postgresql not being able to
keep all the tables/indexes in memory and having to read/write
everything from the disk.

I'll look at the utils anyway and see if there really is unindexed
foreign keys, but from first glance it looks like they're all indexed.

Thanks for your suggestion.

Steve.

________________________________

From: Reece Hart [mailto:reece(at)harts(dot)net]
Sent: Thursday, November 02, 2006 2:53 PM
To: Tom Lane
Cc: Schwenker, Stephen; Michael Fuhr; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] DELETE performance issues

On Thu, 2006-11-02 at 11:27 -0500, Tom Lane wrote:

Are you absolutely sure about that last? Unindexed foreign key
references are the explanation nine times out of ten when
someone
complains about deletes being slow.

This was certainly the major cause when I had slow deletes. (The other
problem was chaining of cascading deletes.) In order to help correct
such problems, I wrote some views to identify unindexed, cascading
foreign keys. An example:

rkh(at)csb-dev=> select * from pgutils.foreign_keys_missing_indexes
;
fk_namespace | fk_relation | fk_column | fk_indexed
| pk_namespace | pk_relation | pk_column | pk_indexed | ud ...

--------------+--------------+---------------------+------------+-------
-------+-------------+-------------+------------+--- ...
gong | node | alias_id | f
| gong | alias | alias_id | t | cn ...
taxonomy | node | division_id | f
| taxonomy | division | division_id | t | cc ...
gong | alias | go_id | f
| gong | node | go_id | t | cc ...
etc...

ud is an abbreviation for update and delete constraint type (cascade,
set null, restrict, etc).

In this view, "indexed" means that the column is the first or only
column in some index, i.e., pg_index.indkey[0] = pg_attribute.attnum. I
suppose that one might want to distinguish the indexing cases more
precisely as unindexed, sole-column index, first col of mult-col index,
second col of multi-col index, etc, but I didn't do so. The views were
originally written for 7.4 and I don't know what's appropriate for
current multicolumn index behavior.

The code is in http://harts.net/reece/pgutils/ .

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0