Can WINDOW be used?

From: Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Can WINDOW be used?
Date: 2010-08-13 16:43:58
Message-ID: AANLkTim5vdL6Q_Y3odgPEQAmBYrOVa=gDKzmNLko2abU@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruce Momjian 2010-08-13 18:17:01 Re: Advice configuring ServeRAID 8k for performance
Previous Message Carlo Stonebanks 2010-08-13 14:51:00 Re: Very bad plan when using VIEW and IN (SELECT...*)