X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D7BBD9DC842 for ; Fri, 20 Jan 2006 13:20:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13605-02 for ; Fri, 20 Jan 2006 13:20:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 5EFD59DC820 for ; Fri, 20 Jan 2006 13:20:26 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 9BEFA3983F; Fri, 20 Jan 2006 11:20:26 -0600 (CST) Date: Fri, 20 Jan 2006 11:20:26 -0600 From: "Jim C. Nasby" To: K C Lau Cc: pgsql-performance@postgresql.org Subject: Re: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX Message-ID: <20060120172026.GF20182@pervasive.com> References: <6.2.1.2.0.20060120120150.08ab7b00@localhost> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6.2.1.2.0.20060120120150.08ab7b00@localhost> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.099 required=5 tests=[AWL=0.099] X-Spam-Score: 0.099 X-Spam-Level: X-Archive-Number: 200601/344 X-Sequence-Number: 16822 On Fri, Jan 20, 2006 at 12:35:36PM +0800, K C Lau wrote: Here's the problem... the estimate for the backwards index scan is *way* off: > -> Limit (cost=0.00..1.26 rows=1 width=4) (actual > time=200032.928..200032.931 rows=1 loops=1) > -> Index Scan Backward using pk_log on > log (cost=0.00..108047.11 rows=86089 width=4) (actual > time=200032.920..200032.920 rows=1 loops=1) > Filter: (((create_time)::text < '2005/10/19'::text) AND > (logsn IS NOT NULL)) > Total runtime: 200051.701 ms BTW, these queries below are meaningless; they are not equivalent to min(logsn). > esdt=> explain analyze select LogSN from Log where create_time < > '2005/10/19' order by create_time limit 1; > > Limit (cost=0.00..0.98 rows=1 width=31) (actual time=0.071..0.073 rows=1 > loops=1) > -> Index Scan using idx_logtime on log (cost=0.00..84649.94 > rows=86089 width=31) (actual time=0.063..0.063 rows=1 loops=1) > Index Cond: ((create_time)::text < '2005/10/19'::text) > Total runtime: 0.182 ms > > esdt=> explain analyze select LogSN from Log where create_time < > '2005/10/19' order by create_time desc limit 1; > Limit (cost=0.00..0.98 rows=1 width=31) (actual time=0.058..0.061 rows=1 > loops=1) > -> Index Scan Backward using idx_logtime on log (cost=0.00..84649.94 > rows=86089 width=31) (actual time=0.051..0.051 rows=1 loops=1) > Index Cond: ((create_time)::text < '2005/10/19'::text) > Total runtime: 0.186 ms > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461