Skip site navigation (1) Skip section navigation (2)

Re: Delete performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: arnaulist(at)andromeiberica(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Delete performance
Date: 2006-02-22 14:31:41
Message-ID: 11337.1140618701@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Arnau <arnaulist(at)andromeiberica(dot)com> writes:
>> The usual cause of slow deletes is that (a) the table is the target of
>> some foreign key references from other large tables, and (b) the
>> referencing columns in those tables aren't indexed.

>    This is a thing I don't understand, as far as I know the foreign keys 
> references to primary keys and postgresql creates itself and index over 
> the primary key, so those columns always should be indexed. Taking into 
> account Tom's observation I'm missing something, could you explain it to 
> all of us :)

The referencED column is forced to have an index.  The referencING
column is not.  The cases where you need an index on the latter are
precisely updates/deletes of the referencED column.

In the old version you are using you can also get burnt by datatype
mismatches --- the foreign key mechanism will allow that as long as
it can find an equality operator for the two types, but that equality
operator might not be indexable.

			regards, tom lane

In response to

Responses

pgsql-admin by date

Next:From: CGDate: 2006-02-22 15:26:11
Subject: WARNING: foreign key constraint will require costly sequential scans during pg_restore
Previous:From: Jayaram BhatDate: 2006-02-22 13:58:46
Subject: unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group