Re: Delete performance - Mailing list pgsql-admin

From Arnau
Subject Re: Delete performance
Date
Msg-id 43FD8E1F.8020704@andromeiberica.com
Whole thread Raw
In response to Re: Delete performance  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Delete performance
List pgsql-admin
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

pgsql-admin by date:

Previous
From: Robin Iddon
Date:
Subject: 8.0.3 pg_autovacuum doesn't clear out stats table?
Next
From: Simon Riggs
Date:
Subject: Re: WAL recovery