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 F009B9235DE for ; Fri, 23 May 2003 13:52:36 -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 38612-10 for ; Fri, 23 May 2003 13:52:15 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id DB5B992322A for ; Fri, 23 May 2003 13:38:40 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4NHccU6017189; Fri, 23 May 2003 13:38:38 -0400 (EDT) To: Vivek Khera Cc: pgsql-general@postgresql.org Subject: Re: [ADMIN] Q: Structured index - which one runs faster? In-reply-to: References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> <11951.1053643980@sss.pgh.pa.us> Comments: In-reply-to Vivek Khera message dated "23 May 2003 11:09:00 -0400" Date: Fri, 23 May 2003 13:38:37 -0400 Message-ID: <17188.1053711517@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/925 X-Sequence-Number: 42580 Vivek Khera writes: > 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? Any of the three indexes can be used for a search on owner_id alone, so yeah, user_list_owner_id is redundant. It would be marginally faster to use user_list_owner_id for such a search, just because it's physically smaller than the other two indexes, but against that you have to balance the extra update cost of maintaining the additional index. Also, I can imagine scenarios where even a pure SELECT query load could find the extra index to be a net loss: if you have a mix of queries that use two or all three indexes, and the indexes don't fit in kernel disk cache but just one or two would, then you'll lose on extra I/O as the indexes compete for cache space. Not sure how likely that scenario is, but it's something to think about. regards, tom lane