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 972C59DCBD7 for ; Thu, 23 Feb 2006 12:35:08 -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 00181-10 for ; Thu, 23 Feb 2006 12:35:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 11D6D9DCB8D for ; Thu, 23 Feb 2006 12:35:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1NGZ06j022826; Thu, 23 Feb 2006 11:35:01 -0500 (EST) To: arnaulist@andromeiberica.com cc: pgsql-admin@postgresql.org 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> Comments: In-reply-to Arnau message dated "Thu, 23 Feb 2006 11:27:43 +0100" Date: Thu, 23 Feb 2006 11:35:00 -0500 Message-ID: <22825.1140712500@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200602/278 X-Sequence-Number: 20699 Arnau writes: >> 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. > 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) Yes, if you are concerned about the performance of updates/deletes on the departments table. The reason the system doesn't make such an index automatically is that there are common scenarios where you seldom or never update the master table, and so the index wouldn't repay the cost it creates for updates of the slave table. regards, tom lane