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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
