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