X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 49B9B9DC833 for ; Thu, 23 Feb 2006 06:27:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16257-06 for ; Thu, 23 Feb 2006 06:27:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id 23C199DC806 for ; Thu, 23 Feb 2006 06:27:43 -0400 (AST) Received: by fafnir.androme.com (Postfix, from userid 1003) id AE78DB92233; Thu, 23 Feb 2006 11:27:44 +0100 (CET) Message-ID: <43FD8E1F.8020704@andromeiberica.com> Date: Thu, 23 Feb 2006 11:27:43 +0100 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org CC: Tom Lane Subject: Re: Delete performance References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> <15417.1140534087@sss.pgh.pa.us> <43FC2A5E.4080601@andromeiberica.com> <11337.1140618701@sss.pgh.pa.us> In-Reply-To: <11337.1140618701@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.123 required=5 tests=[AWL=0.123] X-Spam-Score: 0.123 X-Spam-Level: X-Archive-Number: 200602/272 X-Sequence-Number: 20693 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