Re: DELETE performance issues - Mailing list pgsql-general

From Reece Hart
Subject Re: DELETE performance issues
Date
Msg-id 1162497166.19432.79.camel@snafu.site
Whole thread Raw
In response to Re: DELETE performance issues  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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@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

pgsql-general by date:

Previous
From: AgentM
Date:
Subject: Re: Is there anyway to...
Next
From: "Dan Weber"
Date:
Subject: Re: query takes 65 times longer if I add 1 column (explain attached)