X-Original-To: pgsql-general@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 18FCF9251B7 for ; Fri, 23 May 2003 12:59:49 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 18064-10 for ; Fri, 23 May 2003 12:59:40 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id CE4BC925217 for ; Fri, 23 May 2003 12:48:21 -0400 (EDT) Received: (qmail 11475 invoked by uid 500); 23 May 2003 16:50:20 -0000 Date: Fri, 23 May 2003 11:50:20 -0500 From: Bruno Wolff III To: Vivek Khera Cc: pgsql-general@postgresql.org Subject: Re: [ADMIN] Q: Structured index - which one runs faster? Message-ID: <20030523165020.GA11094@wolff.to> Mail-Followup-To: Vivek Khera , pgsql-general@postgresql.org References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> <11951.1053643980@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/923 X-Sequence-Number: 42578 On Fri, May 23, 2003 at 11:09:00 -0400, Vivek Khera wrote: > A related question: > > Are any of these indexes redundant: > > CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email); > CREATE INDEX user_list_owner_id ON user_list (owner_id); > CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created); > > In particular, is user_list_owner_id redundant to > user_list_oid_created? Will the latter be used for queries such as Yes. Any prefix of a multicolumn index can be used for queries. They (prefixes) won't be usable by foreign key references because even if the index as a whole is unique, the prefixes won't necessarily be.