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 961E792356A for ; Fri, 23 May 2003 11:45:50 -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 68116-10 for ; Fri, 23 May 2003 11:45:41 -0400 (EDT) Received: from lorax.kciLink.com (lorax.kciLink.com [206.112.95.1]) by developer.postgresql.org (Postfix) with ESMTP id 406EA924F62 for ; Fri, 23 May 2003 11:09:12 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by lorax.kciLink.com (Postfix) with ESMTP id 03E7F3E37 for ; Fri, 23 May 2003 11:09:11 -0400 (EDT) Received: from lorax.kciLink.com ([127.0.0.1]) by localhost (lorax.kciLink.com [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 07972-09 for ; Fri, 23 May 2003 11:09:00 -0400 (EDT) Received: by lorax.kciLink.com (Postfix, from userid 8) id A90CD3E8E; Fri, 23 May 2003 11:09:00 -0400 (EDT) To: pgsql-general@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.general Subject: Re: [ADMIN] Q: Structured index - which one runs faster? Date: 23 May 2003 11:09:00 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 27 Message-ID: References: <5.1.1.6.2.20030522223909.05929600@mail.vogelsinger.at> <11951.1053643980@sss.pgh.pa.us> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kciLink.com 1053702540 30602 216.194.193.105 (23 May 2003 15:09:00 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Fri, 23 May 2003 15:09:00 +0000 (UTC) User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Portable Code) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/915 X-Sequence-Number: 42570 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 SELECT user_fname from user_list where owner_id=34 If so, I can drop the owner_id index. the _id columns are integers, created is a datetime, and email is a string. owner_id is also a foreign key into the owners table (via REFERENCES), if that matters. I'd try it out by dropping the index, but reindexing it takes a *LONG* time which I cannot afford to be unavailable. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/