List,
I see benefits to using the 8.4 WINDOW clause in some cases but I'm having trouble seeing if I could morph the following query using it.
wxd0812=# EXPLAIN ANALYZE
wxd0812-# SELECT * FROM
wxd0812-# (SELECT DISTINCT ON (key1_id,key2_id) * FROM sid120.data ORDER BY key1_id,key2_id,time_id DESC) x
wxd0812-# WHERE NOT deleted;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan x (cost=739953.35..787617.03 rows=280375 width=52) (actual time=45520.385..55412.327 rows=391931 loops=1)
Filter: (NOT deleted)
-> Unique (cost=739953.35..782009.53 rows=560750 width=52) (actual time=45520.378..54780.824 rows=591037 loops=1)
-> Sort (cost=739953.35..753972.08 rows=5607490 width=52) (actual time=45520.374..50520.177 rows=5607490 loops=1)
Sort Key: key1_id, key2_id, time_id
-> Seq Scan on data (cost=0.00..111383.90 rows=5607490 width=52) (actual time=0.074..6579.367 rows=5607490 loops=1) Total runtime: 55721.241 ms
(7 rows)
The purpose of this query is to identify the most recent versions of key1_id & key2_id pairs according to time_id which increases over time.
TIA,
Greg
