From: Jim C. Nasby <jnasby@pervasive.com>
To: K C Lau <kclau60@netvigator.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX
Date: Fri, 20 Jan 2006 11:20:26 -0600
Message-ID: <20060120172026.GF20182@pervasive.com> (raw)
In-Reply-To: <6.2.1.2.0.20060120120150.08ab7b00@localhost>
References: <6.2.1.2.0.20060120120150.08ab7b00@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, kclau60@netvigator.com
Subject: Re: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX
In-Reply-To: <20060120172026.GF20182@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox