Re: Deleting, indexes and transactions

On 05/28/2012 07:23 AM, Stevo Slavić wrote:
> Hello PostgreSQL community,
>
> Two tables, A and B, both with auto generated technical PK, A and B are
> in relationship via nullable non-unique FK a_fk column in B to A's PK.
> There are no other relationships involving table A. Lets say A has ~20k
> rows, and B ~500k rows.
>
> When there is no index on a_fk column, if one deletes Bs with DELETE
> FROM b WHERE a_fk IS NOT NULL, and then in same transaction also deletes
> all As - deleting As lasts painfully long.
>
> Adding an index on FK in B, improves A deletion times significantly.
>
> Can someone please provide an explanation/rationale of this behavior,
> why does it take so long to delete As in first case without index?
> Thanks in advance!

It is documented behavior:
http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html

"If the referenced column(s) are changed frequently, it might be wise to
add an index to the foreign key column so that referential actions
associated with the foreign key column can be performed more efficiently."

Though in your case would it not be worth it to just have an ON DELETE
CASCADE clause on your FK?

>
> Btw, I'm using PostgreSQL 9.0. Will try how 9.1 behaves.
>
> Kind regards,
> Stevo.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com