Re: Delete performance

Hi all,

Maybe the direction this thread has taken is a bit out of the scope
of this mailing list, but I think it's very interesting and can be
useful for newbie users.

>
>>>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.

Lets put an example

CREATE TABLE departments
(
id INT2
CONSTRAINT pk_dept_id PRIMARY KEY,
name VARCHAR(50)
CONSTRAINT nn_dept_name NOT NULL
);

CREATE TABLE users
(
id INT8
CONSTRAINT pk_users_id PRIMARY KEY,
name VARCHAR(50)
CONSTRAINT nn_users_name NOT NULL,
department_id INT2
CONSTRAINT fk_users_deptid REFERENCES departments(id)
CONSTRAINT nn_users_deptid NOT NULL
)

Do we should create the following index?

CREATE INDEX idx_users_deptid ON users(department_id)

Could we say as rule of thumb the following: "Create an index for
each table's foreign key"?

Regards
--
Arnau