X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 14443476262 for ; Tue, 4 Mar 2003 12:53:13 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 52076-04 for ; Tue, 4 Mar 2003 12:53:11 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 642AB476228 for ; Tue, 4 Mar 2003 12:53:08 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h24HrDx6027570; Tue, 4 Mar 2003 12:53:13 -0500 (EST) To: Andreas Pflug Cc: pgsql-performance@postgresql.org Subject: Re: Slow query performance on large table In-reply-to: <3E64D694.9050309@web.de> References: <000001c2e25c$ad506170$0c64a8c0@paulspc> <23081.1046790781@sss.pgh.pa.us> <3E64D694.9050309@web.de> Comments: In-reply-to Andreas Pflug message dated "Tue, 04 Mar 2003 17:38:44 +0100" Date: Tue, 04 Mar 2003 12:53:13 -0500 Message-ID: <27569.1046800393@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/18 X-Sequence-Number: 1316 Andreas Pflug writes: > "create index ind_meas on measurement (assessment,time)" should perform > the same as "... primary key(assessment,time)". Sure. > Do possibly non-optimal indices (only assessment, only time as Paul > described earlier) screw up the optimizer, igoring the better option > usiing the PK? One would like to think the optimizer will make the right choice. But using a two-column index just because it's there isn't necessarily the right choice. The two-column index will certainly be bulkier and more expensive to scan, so if there's a one-column index that's nearly as selective, it might be a better choice. regards, tom lane