Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 734C79F93E5 for ; Tue, 5 Dec 2006 23:17:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 15647-04 for ; Tue, 5 Dec 2006 23:17:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lynet.oppetid.no (oppetid.no [80.65.51.2]) by postgresql.org (Postfix) with ESMTP id E27799F93E2 for ; Tue, 5 Dec 2006 23:17:14 -0400 (AST) Received: by lynet.oppetid.no (Postfix, from userid 1019) id 83FDC11AFEB; Wed, 6 Dec 2006 04:17:13 +0100 (CET) Date: Wed, 6 Dec 2006 04:17:13 +0100 From: Tobias Brox To: Tobias Brox Cc: pgsql-performance@postgresql.org Subject: Re: max/min and index usage Message-ID: <20061206031713.GA26712@oppetid.no> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Organization: Group Nordicbet User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/101 X-Sequence-Number: 22157 [Tobias Brox - Wed at 04:01:56AM +0100] > We're using 8.1 - I thought such a construct was safe in pg 8.1: > > select max(indexed_value) from huge_table; > > while earlier we had to use: > > select indexed_value from huge_table order by indexed_value desc limit 1; > > seems like I was wrong: The difference is all about those NULL values ... those columns are quite sparsely populated in the table. The second query gives NULL, which is not much useful :-) However, I made a partial index to solve this problem - this query is able to use the partial index: select indexed_value from huge_table where indexed_value is not NULL order by indexed_value desc limit 1; while this one is not: select max(indexed_value) from huge_table; I guess this is a bug? :-)