470,587 Members | 2,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,587 developers. It's quick & easy.

Slow deletion of data from tables

I have one table with columns that are used as foreign-keys from
several other tables. Sometimes deletion from this table takes +5
seconds for a single row. Are there any utilities that can be used to
figure out why the deletion takes so long? "ANALYZE DELETE FROM foo
WHERE bar=gazonk" doesn't really help as it only explains how the
where statement is resolved, and not what postgres has to do to
preserve database integrity.

Regards,
Nov 23 '05 #1
2 1576
On Fri, 2004-07-02 at 04:35, Rune Froysa wrote:
I have one table with columns that are used as foreign-keys from
several other tables. Sometimes deletion from this table takes +5
seconds for a single row.


Do you have indexes on the foreign key columns in the child tables?

For example, say you have a person table with primary key person_id and
a hobby table where each row uses person_id as a foreign key.

When you create the person table and declare person_id as the primary
key, PostgreSQL will automatically create a unique index on that column.

When you create the hobby table and declare its person_id references
person_id in the person table then PostgreSQL automatically installs
triggers on the hobby table to ensure updates meet the constraint *and*
it installs triggers on the person table to ensure updates there don't
make records in the hobby table invalid. But no extra indexes will be
created.

If you delete from the person table, a trigger will fire and run a query
something like this:

SELECT 1 FROM ONLY hobby x WHERE person_id = $1 FOR UPDATE OF x

If you don't have a (non-unique) index on the person_id column in the
hobby table then this query will require a full table scan.

So, as a rule of thumb, if a table contains a foreign key, you should
create a non-unique index on that column. There may be good reasons not
to bother in certain cases, but it's a good starting point.

Regards
Grant
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Grant McLean <gr***@catalyst.net.nz> writes:
On Fri, 2004-07-02 at 04:35, Rune Froysa wrote:
I have one table with columns that are used as foreign-keys from
several other tables. Sometimes deletion from this table takes +5
seconds for a single row.
Do you have indexes on the foreign key columns in the child tables?


Also: if you do have indexes on the referenced columns, they may still
not get used because of datatype mismatches. It's generally a good idea
to make sure that referenced and referencing columns of a foreign-key
constraint have exactly the same datatype.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by DJJ | last post: by
4 posts views Thread by hennovanrensburg | last post: by
3 posts views Thread by UJ | last post: by
12 posts views Thread by grace | last post: by
2 posts views Thread by existential.philosophy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.