Thread: Can WINDOW be used?

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

> 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;

SELECT * FROM (
     SELECT data.*,
       rank() as rank over
            ( partition by key1_id, key2_id order by time_id DESC )
     FROM data
) as rankings
WHERE rank = 1 AND NOT deleted;


--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com