Message-ID: <436A2F9A.4050202@slamb.org>
Date: Thu, 03 Nov 2005 07:41:14 -0800
From: Scott Lamb <slamb@slamb.org>
User-Agent: Thunderbird 1.4.1 (Macintosh/20051006)
MIME-Version: 1.0
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org,
	Dustin Sallings <dustin@spy.net>
Subject: Re: Sorted union
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD78D@Herge.rcsinc.local>
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD78D@Herge.rcsinc.local>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Merlin Moncure wrote:
> hmm, try pushing the union into a subquery...this is better style
> because it's kind of ambiguous if the ordering will apply before/after
> the union.

Seems to be a little slower. There's a new "subquery scan" step.

     explain analyze
     select    q.when_happened from (
     select    when_stopped as when_happened,
               1 as order_hint
     from      transaction t
     where     '2005-10-25 15:00:00' <= when_stopped
       and     when_stopped <= '2005-10-26 10:00:00'
     union all
     select    when_stopped as when_happened,
               2 as order_hint
     from      transaction t
     where     '2005-10-25 15:00:00' <= when_stopped
       and     when_stopped <= '2005-10-26 10:00:00'
     ) q order by  when_happened, order_hint;

 
                QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=713013.96..721751.25 rows=3494916 width=12) (actual 
time=34392.264..37237.148 rows=3364006 loops=1)
    Sort Key: when_happened, order_hint
    ->  Subquery Scan q  (cost=0.00..229474.11 rows=3494916 width=12) 
(actual time=0.194..20283.452 rows=3364006 loops=1)
          ->  Append  (cost=0.00..194524.95 rows=3494916 width=8) 
(actual time=0.191..14967.632 rows=3364006 loops=1)
                ->  Subquery Scan "*SELECT* 1"  (cost=0.00..97262.48 
rows=1747458 width=8) (actual time=0.189..5535.139 rows=1682003 loops=1)
                      ->  Index Scan using transaction_stopped on 
"transaction" t  (cost=0.00..79787.90 rows=1747458 width=8) (actual 
time=0.186..3097.268 rows=1682003 loops=1)
                            Index Cond: (('2005-10-25 
15:00:00'::timestamp without time zone <= when_stopped) AND 
(when_stopped <= '2005-10-26 10:00:00'::timestamp without time zone))
                ->  Subquery Scan "*SELECT* 2"  (cost=0.00..97262.48 
rows=1747458 width=8) (actual time=0.173..5625.155 rows=1682003 loops=1)
                      ->  Index Scan using transaction_stopped on 
"transaction" t  (cost=0.00..79787.90 rows=1747458 width=8) (actual 
time=0.169..3146.714 rows=1682003 loops=1)
                            Index Cond: (('2005-10-25 
15:00:00'::timestamp without time zone <= when_stopped) AND 
(when_stopped <= '2005-10-26 10:00:00'::timestamp without time zone))
  Total runtime: 39775.225 ms
(11 rows)

> question: why do you want to flatten the table...is it not easier to
> work with as records?

For most things, yes. But I'm making a bunch of different graphs from 
these data, and a few of them are much easier with events. The best 
example is my concurrency graph. Whenever there's a start event, it goes 
up one. Whenever there's a stop event, it goes down one. It's completely 
trivial once you have it separated into events.

Thanks,
Scott