The nested view from hell - Restricting a subquerry - Mailing list pgsql-sql

I've got a legacy app with a hefty performance problem.  The basic problem is stupid design. It takes 10-15 seconds of
CPUtime to look up an invoice.<br /> Basically it's trying to mash up extra columns on an otherwise simple query, and
thoseextra columns are subtotals.  Simplified (this looks best in a fixed width font):<br /><br /><tt>    SELECT 
max(order_view.order_id),max(order_view.invoice_id),sum(order_view.mileage)<br />     FROM    (SELECT
order_id,invoice_id,0 as miles FROM eg_order<br />              UNION    <br />              SELECT order_id,0        
,     miles FROM eg_order_line)<br />              order_view GROUP BY order_view.order_id;<br /></tt><br /> A select
byorder_id is fast.  The problem is the application uses "select * from view where invoice_id=x", and the second part
ofthe UNION returns all possible rows in the database.  These get filtered out later, but at considerable performance
hit.<br/><br /> Is there a way to get the "where invoice_id=x" into the subquery?  "select distinct order_id from
eg_orderwhere invoice_id=x" would do it.<br /> I can't redesign the view, because it all goes into an object relational
mapperthat thinks it's a real table.<br /><br />                    -Bryce Nesbitt<br /><br /><br /><tt>stage=# \d
eg_invoice_summary_view<br/>         View "public.eg_invoice_summary_view"<br />      Column     |         
Type         | Modifiers <br /> ----------------+------------------------+-----------<br />  invoice_id     |
integer               | <br />  cso_id         | integer                | <br />  period_id      |
integer               | <br />  account_id     | integer                | <br />  invoice_number | character
varying(192)| <br />  invoice_date   | date                   | <br />  amount         | numeric                | <br
/> tax            | bigint                 | <br />  invoice_style  | integer                | <br />  plan_name      |
charactervarying(128) | <br /> View definition:<br />  SELECT i.invoice_id, i.cso_id, i.period_id, i.account_id,
i.invoice_number,i.invoice_date, i.amount, sum(tax.tax_amount) AS tax, i.invoice_style, i.plan_name<br />    FROM (
SELECTi.invoice_id, i.cso_id, i.period_id, i.account_id, i.invoice_number, i.invoice_date, sum(o.amount) AS amount,
i.invoice_style,i.plan_name<br />            FROM eg_invoice i<br />       LEFT JOIN <b>eg_order_summary_view</b> o ON
i.invoice_id= o.invoice_id<br />      GROUP BY i.invoice_id, i.cso_id, i.period_id, i.account_id, i.invoice_number,
i.invoice_date,i.invoice_style, i.plan_name) i<br />    LEFT JOIN eg_invoice_tax tax ON i.invoice_id =
tax.invoice_id<br/>   GROUP BY i.invoice_id, i.cso_id, i.period_id, i.account_id, i.invoice_number, i.invoice_date,
i.amount,i.invoice_style, i.plan_name;<br /><br /> stage=# \d eg_order_summary_view<br />         View
"public.eg_order_summary_view"<br/>    Column   |           Type           | Modifiers <br />
------------+--------------------------+-----------<br/>  order_id   | integer                  | <br />  d          |
"unknown"               | <br />  cso_id     | integer                  | <br />  invoice_id | integer                 
|<br />  period_id  | integer                  | <br />  ref_id     | integer                  | <br />  order_type |
integer                 | <br />  desc1      | text                     | <br />  desc2      | text                    
|<br />  desc3      | text                     | <br />  desc4      | text                     | <br />  desc5      |
text                    | <br />  desc6      | text                     | <br />  desc7      | text                    
|<br />  desc8      | text                     | <br />  order_from | timestamp with time zone | <br />  order_to   |
timestampwith time zone | <br />  hours      | double precision         | <br />  mileage    | double precision        
|<br />  amount     | bigint                   | <br /> View definition:<br />  SELECT <b>order_view.order_id</b>, 'D'
ASd, max(order_view.cso_id) AS cso_id, <b>max(order_view.invoice_id) AS invoice_id</b>, max(order_view.period_id) AS
period_id,max(order_view.ref_id) AS ref_id, max(order_view.order_type) AS order_type, max(order_view.desc1::text) AS
desc1,max(order_view.desc2::text) AS desc2, max(order_view.desc3::text) AS desc3, max(order_view.desc4::text) AS desc4,
max(order_view.desc5::text)AS desc5, max(order_view.desc6::text) AS desc6, max(order_view.desc7::text) AS desc7,
max(order_view.desc8::text)AS desc8, max(order_view.order_from) AS order_from, max(order_view.order_to) AS order_to,
sum(order_view.hours)AS hours, sum(order_view.mileage) AS mileage, sum(order_view.amount) AS amount<br />    FROM (
SELECTeg_order.order_id, eg_order.cso_id, e<b>g_order.invoice_id</b>, eg_order.period_id, eg_order.ref_id,
eg_order.order_type,eg_order.desc1, eg_order.desc2, eg_order.desc3, eg_order.desc4, eg_order.desc5, eg_order.desc6,
eg_order.desc7,eg_order.desc8, eg_order.order_from, eg_order.order_to, 0 AS hours, 0 AS mileage, 0 AS amount<br />
          FROM eg_order<br /> UNION <br />         ( SELECT <b>eg_order_line.order_id</b>, 0, <b>0</b>, 0, 0, 0,
NULL::"unknown"AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown",
NULL::"unknown"AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown",
to_timestamp('1970-01-01'::text,'YYYY-MM-DD'::text) AS to_timestamp, to_timestamp('1970-01-01'::text,
'YYYY-MM-DD'::text)AS to_timestamp, 0 AS hours, eg_order_line.quantity AS mileage, eg_order_line.amt_value<br />
          FROM eg_order_line<br />           WHERE eg_order_line.order_line_type = 20<br /> UNION <br />         
SELECT<b>eg_order_line.order_id</b>, 0, <b>0,</b> 0, 0, 0, NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown",
NULL::"unknown"AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown", NULL::"unknown" AS "unknown",
NULL::"unknown"AS "unknown", NULL::"unknown" AS "unknown", to_timestamp('1970-01-01'::text, 'YYYY-MM-DD'::text) AS
to_timestamp,to_timestamp('1970-01-01'::text, 'YYYY-MM-DD'::text) AS to_timestamp, eg_order_line.quantity AS hours, 0
ASmileage, eg_order_line.amt_value<br />            FROM eg_order_line<br />           WHERE
eg_order_line.order_line_type<> 20)) order_view<br />   <b>GROUP BY order_view.order_id</b>;<br /><br /> stage=#
explainselect * from eg_invoice_summary_view where invoice_id=5;<br />
                                                                                                              <br />
   QUERY PLAN                                                                                                 <br />
                  <br />
---------------------------------------------------------------------------------------------------------------<br/>
---------------------------------------------------------------------------------------------------------------<br/>
-------------------<br/>  GroupAggregate  <b>(cost=485551.60..485551.80 rows=1 width=736)</b><br />    ->  Sort 
(cost=485551.60..485551.61rows=7 width=736)<br />          Sort Key: i.invoice_id, i.cso_id, i.period_id, i.account_id,
i.invoice_number,i.invoice_date, i.amoun<br /> t, i.invoice_style, i.plan_name<br />          ->  Nested Loop Left
Join (cost=485541.78..485551.50 rows=7 width=736)<br />                ->  HashAggregate  (cost=485540.75..485540.77
rows=1width=56)<br />                      ->  Nested Loop Left Join  (cost=417895.29..485536.25 rows=200
width=56)<br/>                            ->  Index Scan using eg_invoice_pkey on eg_invoice i  (cost=0.00..3.01
rows=1width=<br /> 48)<br />                                  Index Cond: (invoice_id = 5)<br />
                          ->  GroupAggregate  (cost=417895.29..485529.24 rows=200 width=316)<br />
                                Filter: (max("?column3?") = 5)<br />                                  ->  Unique 
(cost=417895.29..448632.54rows=614745 width=200)<br />                                        ->  Sort 
(cost=417895.29..419432.15rows=614745 width=200)<br />                                              Sort Key: order_id,
cso_id,invoice_id, period_id, ref_id, order_t<br /> ype, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8,
order_from,order_to, hours, mileage, amount<br />                                              ->  Append 
(cost=0.00..106638.70rows=614745 width=200)<br />                                                    ->  Subquery
Scan"*SELECT* 1"  (cost=0.00..9621.64 rows=233<br /> 432 width=200)<br />
                                                        ->  Seq Scan on eg_order  (cost=0.00..7287.32 rows=233<br />
432width=200)<br />                                                    ->  Result  (cost=77951.41..97017.06
rows=381313width=16)<br />                                                          ->  Unique 
(cost=77951.41..97017.06rows=381313 width<br /> =16)<br />
                                                              ->  Sort  (cost=77951.41..78904.69 rows=381313 w<br />
idth=16)<br/>                                                                      Sort Key: order_id, cso_id,
invoice_id,pe<br /> riod_id, ref_id, order_type, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, order_from,
order_to,hour<br /> s, mileage, amount<br />                                                                     
-> Append  (cost=0.00..25112.52 rows=3813<br /> 13 width=16)<br />
                                                                          ->  Subquery Scan "*SELECT* 2"  (cos<br />
t=0.00..11887.06rows=146043 width=16)<br />
                                                                                ->  Seq Scan on eg_order_line <br />
 (cost=0.00..10426.63rows=146043 width=16)<br />
                                                                                      Filter: (order_line_type<br />  =
20)<br/>                                                                            ->  Subquery Scan "*SELECT* 3" 
(cos<br/> t=0.00..13225.46 rows=235270 width=16)<br />
                                                                                ->  Seq Scan on eg_order_line <br />
 (cost=0.00..10872.76rows=235270 width=16)<br />
                                                                                      Filter: (order_line_type<br />
 <>20)<br />                ->  Bitmap Heap Scan on eg_invoice_tax tax  (cost=1.03..10.65 rows=7 width=8)<br
/>                     Recheck Cond: (invoice_id = 5)<br />                      ->  Bitmap Index Scan on
ix2f10773c8edf278d (cost=0.00..1.03 rows=7 width=0)<br />                            Index Cond: (invoice_id = 5)<br />
(31rows)<br /><br /></tt><br /><br /><pre class="moz-signature" cols="100">-- 
 
----
Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>