From: Shaun Thomas <sthomas@peak6.com>
To: Dimi Paun <dimi@lattica.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: How to use indexes for GROUP BY
Date: Mon, 24 Jan 2011 13:27:38 -0600
Message-ID: <4D3DD2AA.3070500@peak6.com> (raw)
In-Reply-To: <1295893741.9380.984.camel@dimi.lattica.com>
References: <1295893741.9380.984.camel@dimi.lattica.com>
On 01/24/2011 12:29 PM, Dimi Paun wrote:
> I want to simply get the latest "creationTS" for each location,
> but that seems to result in a full table scan:
>
> tts_server_db=# explain analyze select location, max(creationTS) from
> tagrecord group by location;
Try this, it *might* work:
select DISTINCT ON (location) location, creationTS
from tagrecord
order by location, creationTS DESC;
Secondly... Postgresql 8.1? Really? If at all possible, upgrade. There
is a lot you're missing from the last six years of PostgreSQL releases.
For instance, your MAX means a reverse index scan for each location,
which is far more expensive than an ordered index scan, so the planner
may be ignoring it, if the planner in 8.1 is even that intelligent.
If you were running 8.3, for instance, your index could be:
create index idx_tagdata_loc_creationTS on tagRecord(location,
creationTS DESC);
And then suddenly it just has to use the first match for that index for
each location. Older PG versions are... flaky when it comes to
optimization. I'm not sure if 8.1 used MAX as an internal construct or
treated it like a function. If it's the latter, it has to read every
value to find out which is the "max", which is why using ORDER BY *may*
fix your problem.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
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: sthomas@peak6.com, dimi@lattica.com
Subject: Re: How to use indexes for GROUP BY
In-Reply-To: <4D3DD2AA.3070500@peak6.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