Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5CB7F64FDEF for ; Wed, 12 Nov 2008 15:39:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 95955-04 for ; Wed, 12 Nov 2008 15:39:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from depesz.com (depesz.com [78.46.78.198]) by mail.postgresql.org (Postfix) with ESMTP id 79F0E64FD9B for ; Wed, 12 Nov 2008 15:39:24 -0400 (AST) Received: from depesz by depesz.com with local (Exim 4.69) (envelope-from ) id 1L0LYd-00027H-1P; Wed, 12 Nov 2008 20:39:19 +0100 Date: Wed, 12 Nov 2008 20:39:19 +0100 From: hubert depesz lubaczewski To: Andrus Cc: pgsql-performance@postgresql.org Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed Message-ID: <20081112193918.GA7752@depesz.com> Reply-To: depesz@depesz.com References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/105 X-Sequence-Number: 31836 On Wed, Nov 12, 2008 at 07:02:10PM +0200, Andrus wrote: > explain analyze select max(kuupaev) from bilkaib where > kuupaev<=date'2008-11-01' and (cr='00' or db='00') do you always have this: "(cr='00' or db='00')"? or do the values (00) change? if they don't change, or *most* of the queries have "(cr='00' or db='00')", than the biggest time difference you will get after creating this index: create index test on bilkaib (kuupaev) where cr='00' or db='00'; depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007