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