Message-ID: <4C489AFE.2060601@archonet.com>
Date: Thu, 22 Jul 2010 20:24:46 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100217 Lightning/1.0b1 Shredder/3.0.3pre
MIME-Version: 1.0
To: Wes Devauld <wes@devauld.ca>
CC: pgsql-sql@postgresql.org
Subject: Re: Aggregates (last/first) not behaving
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
	<4C480508.8080306@archonet.com>
	<AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
In-Reply-To: <AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 22/07/10 16:50, Wes Devauld wrote:
>
> I was searching for a way to keep using last() and keeping the extraction to
> a single step, although the more I fight with it, the less I think that it
> is worth it.  If you have any further suggestions, I would appreciate
> hearing them.

You can certainly do it in a single query. I've commented out the 
event_date_idx below because it's far from guaranteed it'll be useful to 
you.

BEGIN;

DROP TABLE IF EXISTS events;

CREATE TABLE events (
     e_id   SERIAL,
     e_ts   timestamp(0) without time zone,
     PRIMARY KEY (e_id)
);

INSERT INTO events (e_ts)
SELECT '2010-01-01 01:01:01'::timestamp without time zone
+ i * '1 minute'::interval
FROM generate_series(0,999999) i;

-- CREATE INDEX event_date_idx ON events ((e_ts::date),e_ts);
CREATE INDEX events_ts_idx ON events (e_ts);

-- EXPLAIN ANALYSE
SELECT
     e.e_id,
     e.e_ts,
     minmax.tgt_day
FROM (
     SELECT
         (e_ts::date) AS tgt_day,
          min(e_ts) as first_ts,
          max(e_ts) as last_ts
     FROM
         events
     GROUP BY 1
) AS minmax
JOIN events e
ON (e.e_ts = minmax.first_ts) OR (e.e_ts = minmax.last_ts)
ORDER BY e_ts
;

COMMIT;

Using the real, windowing versions of first/last in 8.4+ will still 
require sorting the whole table (AFAICT) so isn't likely to be much 
improvement over a self-join here.

-- 
   Richard Huxton
   Archonet Ltd