Skip site navigation (1) Skip section navigation (2)

Re: select max() much slower than select min()

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: "Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us]" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Kevin Grittner [Kevin(dot)Grittner(at)wicourts(dot)gov]" <Kevin(dot)Grittner(at)wicourts(dot)gov>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: select max() much slower than select min()
Date: 2009-06-19 20:05:46
Message-ID: 4A3BEF9A.4000201@ca.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] wrote:
> Some experimentation suggests that it might help to provide a 2-column
> index on (ts_id, ts_interval_start_time).  This is still going to be
> scanned in order by ts_id, but it will be possible to check the
> ts_interval_start_time condition in the index, eliminating a large
> number of useless trips to the heap.  Whether this type of query is
> important enough to justify maintaining an extra index for is something
> you'll have to decide for yourself...

Thanks to all for the analysis and suggestions. Since the number of rows 
in an hour < ~500,000, brute force looks to be a fast solution:

select ts_id from ... where ts_interval_start_time >= ... and ...

This query runs very fast as does a single pass through the ids to find 
the min and max.

Brian

Responses

pgsql-performance by date

Next:From: David ReesDate: 2009-06-19 20:41:39
Subject: Re: select max() much slower than select min()
Previous:From: Alan McKayDate: 2009-06-19 19:48:48
Subject: Re: processor running queue - general rule of thumb?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group