Message-ID: <46A84D78.8020200@obviously.com>
Date: Thu, 26 Jul 2007 00:30:00 -0700
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.4) Gecko/20061107 SUSE/1.1.2-1.1 SeaMonkey/1.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
References: <46A2CABF.3080108@obviously.com> <f7v6na$2gc$1@sea.gmane.org>
	<2385.1185127507@sss.pgh.pa.us>
In-Reply-To: <2385.1185127507@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

One down.  Total runtime of the simplest query went from 34661.572 ms to
.634 ms (45,000 times faster).

stage=> explain analyze select * from eg_order_summary_view where
invoice_id=1432655;
                                                                  QUERY
PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=47.75..48.60 rows=13 width=214) (actual
time=0.444..0.467 rows=9 loops=1)
   ->  Nested Loop Left Join  (cost=0.00..46.76 rows=21 width=214)
(actual time=0.037..0.175 rows=14 loops=1)
         ->  Index Scan using ix522779518edf278d on eg_order 
(cost=0.00..4.70 rows=13 width=200) (actual time=0.020..0.034 rows=9
loops=1)
               Index Cond: (invoice_id = 1432655)
         ->  Index Scan using ixf8331222783867cc on eg_order_line 
(cost=0.00..3.21 rows=2 width=18) (actual time=0.007..0.010 rows=2 loops=9)
               Index Cond: ("outer".order_id = eg_order_line.order_id)
 Total runtime: 0.645 ms
(7 rows)

stage=> \d eg_order_summary_view;
View definition:
 SELECT eg_order.order_id, 'D' AS d, max(eg_order.cso_id) AS cso_id,
eg_order.invoice_id, max(eg_order.period_id) AS period_id,
max(eg_order.ref_id) AS ref_id, max(eg_order.order_type::integer) AS
order_type, max(eg_order.desc1::text) AS desc1,
max(eg_order.desc2::text) AS desc2, max(eg_order.desc3::text) AS desc3,
max(eg_order.desc4::text) AS desc4, max(eg_order.desc5::text) AS desc5,
max(eg_order.desc6::text) AS desc6, max(eg_order.desc7::text) AS desc7,
max(eg_order.desc8::text) AS desc8, max(timezone('PST8PDT'::text,
eg_order.order_from)) AS order_from, max(timezone('PST8PDT'::text,
eg_order.order_to)) AS order_to, sum(
        CASE
            WHEN eg_order_line.order_line_type <> 20 THEN
eg_order_line.quantity
            ELSE 0::double precision
        END) AS hours, sum(
        CASE
            WHEN eg_order_line.order_line_type = 20 THEN
eg_order_line.quantity
            ELSE 0::double precision
        END) AS mileage, sum(eg_order_line.amt_value) AS amount
   FROM eg_order
   LEFT JOIN eg_order_line USING (order_id)
  GROUP BY eg_order.order_id, eg_order.invoice_id;