pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arnau <arnaulist@andromeiberica.com>
To: pgsql-admin@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Delete performance
Date: Thu, 23 Feb 2006 11:27:43 +0100
Message-ID: <43FD8E1F.8020704@andromeiberica.com> (raw)
In-Reply-To: <11337.1140618701@sss.pgh.pa.us>
References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com>
	<15417.1140534087@sss.pgh.pa.us>
	<43FC2A5E.4080601@andromeiberica.com>
	<11337.1140618701@sss.pgh.pa.us>

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



view thread (7+ messages)  latest in thread

Message-ID: <43FD8E1F.8020704@andromeiberica.com>
Permalink:  ../43FD8E1F.8020704@andromeiberica.com/
Also on:    postgresql.org/message-id/43FD8E1F.8020704@andromeiberica.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: arnaulist@andromeiberica.com, tgl@sss.pgh.pa.us
  Subject: Re: Delete performance
  In-Reply-To: <43FD8E1F.8020704@andromeiberica.com>
  References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> <15417.1140534087@sss.pgh.pa.us> <43FC2A5E.4080601@andromeiberica.com> <11337.1140618701@sss.pgh.pa.us> <43FD8E1F.8020704@andromeiberica.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox