From: hubert depesz lubaczewski <depesz@depesz.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 21:07:36 +0100
Message-ID: <20110124200735.GA23732@depesz.com> (raw)
In-Reply-To: <1295893741.9380.984.camel@dimi.lattica.com>
References: <1295893741.9380.984.camel@dimi.lattica.com>
On Mon, Jan 24, 2011 at 01:29:01PM -0500, Dimi Paun wrote:
> Hi folks,
>
> I have a table like so:
>
> create table tagRecord (
> uid varchar(60) primary key,
> [bunch of other fields]
> location varchar(32),
> creationTS timestamp
> );
> create index idx_tagdata_loc_creationTS on tagRecord(location, creationTS);
>
> The number of individual values in location is small (e.g. 2).
>
> 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;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=5330.53..5330.55 rows=2 width=18) (actual time=286.161..286.165 rows=3 loops=1)
> -> Seq Scan on tagrecord (cost=0.00..4771.35 rows=111835 width=18) (actual time=0.059..119.828 rows=111739 loops=1)
> Total runtime: 286.222 ms
you can use technique described in here:
http://www.depesz.com/index.php/2009/07/10/getting-list-of-unique-elements/
Best regards,
depesz
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: depesz@depesz.com, dimi@lattica.com
Subject: Re: How to use indexes for GROUP BY
In-Reply-To: <20110124200735.GA23732@depesz.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