| From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
|---|---|
| To: | <depesz(at)depesz(dot)com> |
| Cc: | "JS Ubei" <jsubei(at)yahoo(dot)fr>, <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: optimize query with a maximum(date) extraction |
| Date: | 2007-09-05 14:14:00 |
| Message-ID: | 871wddqjx3.fsf@oxford.xeocode.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
"hubert depesz lubaczewski" <depesz(at)depesz(dot)com> writes:
> On Wed, Sep 05, 2007 at 12:30:21PM +0100, Gregory Stark wrote:
>> SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id, the_date ASC
>> SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id, the_date DESC
>> I think the first of these can actually use your index but the latter can't
>> unless you create one for it specifically (which is not so easy -- it'll be
>> easier in 8.3 though). Worse, I'm not really sure it'll be any faster than the
>> query you already have.
>
> it's easy to fix the second query (fix to use index) - just change order
> by to:
> order by id desc, the_date desc.
Cute. I didn't think of that, thanks
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gregory Stark | 2007-09-05 14:23:23 | Re: optimize query with a maximum(date) extraction |
| Previous Message | hubert depesz lubaczewski | 2007-09-05 13:22:50 | Re: optimize query with a maximum(date) extraction |