Re: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux