From: Gregory Stark <stark@enterprisedb.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimize query with a maximum(date) extraction
Date: Wed, 05 Sep 2007 15:28:53 +0100
Message-ID: <87ps0xp4nu.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <162867790709050548l3e1fd155g40b745f2ae42834e@mail.gmail.com>
References: <302443.65402.qm@web26209.mail.ukl.yahoo.com>
<87abs1qrhu.fsf@oxford.xeocode.com>
<87642pqpue.fsf@oxford.xeocode.com>
<a2de01dd0709050543j57de8774s9e9e0a576cc21bd1@mail.gmail.com>
<162867790709050548l3e1fd155g40b745f2ae42834e@mail.gmail.com>
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
>>
>> why not
>>
>> select id,
>> min(the_date) as min_date,
>> max(the_date) as max_date
>> from my_table group by id;
>>
>> Since 8.0 or was it earlier this will use an index should a reasonable one
>> exist.
As I mentioned in the other post that's not true for this query.
> without any limits, seq scan is optimal.
That's not necessarily true either. You could have ten distinct ids and
millions of dates for them. In that case a scan of the index which jumped
around to scan from the beginning and end of each distinct id value would be
faster. There's just no such plan type in Postgres currently.
You can simulate such a plan with the subqueries I described but there's a bit
more overhead than necessary and you need a reasonably efficient source of the
distinct ids. Also it may or may not be faster than simply scanning the whole
table like above and simulating it with subqueries makes it impossible to
choose the best plan.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, pavel.stehule@gmail.com, peterachilds@gmail.com
Subject: Re: optimize query with a maximum(date) extraction
In-Reply-To: <87ps0xp4nu.fsf@oxford.xeocode.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