X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 21801476154 for ; Tue, 4 Mar 2003 11:38:39 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 29649-05 for ; Tue, 4 Mar 2003 11:38:37 -0500 (EST) Received: from smtp.web.de (smtp02.web.de [217.72.192.151]) by postgresql.org (Postfix) with ESMTP id 5E457475ED1 for ; Tue, 4 Mar 2003 11:38:37 -0500 (EST) Received: from p508186ad.dip0.t-ipconnect.de ([80.129.134.173] helo=web.de) by smtp.web.de with asmtp (TLSv1:RC4-MD5:128) (WEB.DE(Exim) 4.97 #53) id 18qFRA-0002Xu-00 for pgsql-performance@postgresql.org; Tue, 04 Mar 2003 17:38:40 +0100 Message-ID: <3E64D694.9050309@web.de> Date: Tue, 04 Mar 2003 17:38:44 +0100 From: Andreas Pflug User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Slow query performance on large table References: <000001c2e25c$ad506170$0c64a8c0@paulspc> <23081.1046790781@sss.pgh.pa.us> In-Reply-To: <23081.1046790781@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/10 X-Sequence-Number: 1308 Tom Lane wrote: >"Paul McKay" writes: > > >>The query I am executing is >>Select time,value >>From measurement >>Where assessment = ? >>And time between ? and ? >> >> > >EXPLAIN ANALYZE would help you investigate this. Is it using an >indexscan? On which index? Does forcing use of the other index >(by temporarily dropping the preferred one) improve matters? > >Possibly a two-column index on both assessment and time would be >an improvement, but it's hard to guess without knowing anything >about the selectivity of the two WHERE clauses. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- > > Tom, does this mean that a primary key alone might not be enough? As far as I understood Paul, the PK looks quite as the newly created index does, so "create index ind_meas on measurement (assessment,time)" should perform the same as "... primary key(assessment,time)". Do possibly non-optimal indices (only assessment, only time as Paul described earlier) screw up the optimizer, igoring the better option usiing the PK? Obviously, the index used should be combined of (assessment,time) but IMHO a PK should be enough. regards, Andreas