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

Re: efficient data reduction (and deduping)

From: Peter van Hardenberg <pvh(at)pvh(dot)ca>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: efficient data reduction (and deduping)
Date: 2012-03-01 18:40:20
Message-ID: CAAcg=kVXjbd_CSaMQUB5iigXO5x+7QyX12z8xHueHd4_15p7nQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Mar 1, 2012 at 10:27 AM, Alessandro Gagliardi
<alessandro(at)path(dot)com> wrote:
> Now, I want to reduce that data to get the last activity that was performed
> by each user in any given hour. It should fit into a table like this:
>

How about:

1) Create an expression based index on date_trunc('hour', hr_timestamp)
2) Create a view on that showing the last value
3) If you want to throw away the data use CREATE TABLE AS on the
results of the view.

You may also want to investigate window functions.

-p

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt." -- Kurt Vonnegut

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-03-01 18:51:42
Subject: Re: efficient data reduction (and deduping)
Previous:From: Craig JamesDate: 2012-03-01 18:35:27
Subject: Re: efficient data reduction (and deduping)

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