Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 248A09FB279 for ; Sun, 22 Jul 2007 00:22:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 00402-01 for ; Sun, 22 Jul 2007 00:22:03 -0300 (ADT) X-Greylist: delayed 00:11:03.099162 by SQLgrey-1.7.5 Received: from outF.internet-mail-service.net (outF.internet-mail-service.net [216.240.47.229]) by postgresql.org (Postfix) with ESMTP id AA6A39FB216 for ; Sun, 22 Jul 2007 00:22:01 -0300 (ADT) Received: from mx0.idiom.com (HELO idiom.com) (216.240.32.160) by out.internet-mail-service.net (qpsmtpd/0.32) with ESMTP; Sat, 21 Jul 2007 20:10:57 -0700 Received: from [192.168.1.109] (adsl-71-135-174-255.dsl.pltn13.pacbell.net [71.135.174.255]) by idiom.com (Postfix) with ESMTP id 75DE5125A2A for ; Sat, 21 Jul 2007 20:10:56 -0700 (PDT) Message-ID: <46A2CABF.3080108@obviously.com> Date: Sat, 21 Jul 2007 20:10:55 -0700 From: Bryce Nesbitt 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: The nested view from hell - Restricting a subquerry Content-Type: multipart/alternative; boundary="------------040701090906080406070000" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/123 X-Sequence-Number: 28858 This is a multi-part message in MIME format. --------------040701090906080406070000 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit I've got a legacy app with a hefty performance problem. The basic problem is stupid design. It takes 10-15 seconds of CPU time to look up an invoice. Basically it's trying to mash up extra columns on an otherwise simple query, and those extra columns are subtotals. Simplified (this looks best in a fixed width font): SELECT max(order_view.order_id),max(order_view.invoice_id) ,sum(order_view.mileage) FROM (SELECT order_id,invoice_id, 0 as miles FROM eg_order UNION SELECT order_id,0 , miles FROM eg_order_line) order_view GROUP BY order_view.order_id; A select by order_id is fast. The problem is the application uses "select * from view where invoice_id=x", and the second part of the UNION returns all possible rows in the database. These get filtered out later, but at considerable performance hit. Is there a way to get the "where invoice_id=x" into the subquery? "select distinct order_id from eg_order where invoice_id=x" would do it. I can't redesign the view, because it all goes into an object relational mapper that thinks it's a real table. -Bryce Nesbitt stage=# \d eg_invoice_summary_view View "public.eg_invoice_summary_view" Column | Type | Modifiers ----------------+------------------------+----------- invoice_id | integer | cso_id | integer | period_id | integer | account_id | integer | invoice_number | character varying(192) | invoice_date | date | amount | numeric | tax | bigint | invoice_style | integer | plan_name | character varying(128) | View definition: 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 FROM ( SELECT i.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 FROM eg_invoice i LEFT JOIN *eg_order_summary_view* o ON i.invoice_id = o.invoice_id 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 LEFT JOIN eg_invoice_tax tax ON i.invoice_id = tax.invoice_id 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; stage=# \d eg_order_summary_view View "public.eg_order_summary_view" Column | Type | Modifiers ------------+--------------------------+----------- order_id | integer | d | "unknown" | cso_id | integer | invoice_id | integer | period_id | integer | ref_id | integer | order_type | integer | desc1 | text | desc2 | text | desc3 | text | desc4 | text | desc5 | text | desc6 | text | desc7 | text | desc8 | text | order_from | timestamp with time zone | order_to | timestamp with time zone | hours | double precision | mileage | double precision | amount | bigint | View definition: SELECT *order_view.order_id*, 'D' AS d, max(order_view.cso_id) AS cso_id, *max(order_view.invoice_id) AS invoice_id*, 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 FROM ( SELECT eg_order.order_id, eg_order.cso_id, e*g_order.invoice_id*, 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 FROM eg_order UNION ( SELECT *eg_order_line.order_id*, 0, *0*, 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 FROM eg_order_line WHERE eg_order_line.order_line_type = 20 UNION SELECT *eg_order_line.order_id*, 0, *0,* 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 AS mileage, eg_order_line.amt_value FROM eg_order_line WHERE eg_order_line.order_line_type <> 20)) order_view *GROUP BY order_view.order_id*; stage=# explain select * from eg_invoice_summary_view where invoice_id=5; QUERY PLAN --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------- GroupAggregate *(cost=485551.60..485551.80 rows=1 width=736)* -> Sort (cost=485551.60..485551.61 rows=7 width=736) Sort Key: i.invoice_id, i.cso_id, i.period_id, i.account_id, i.invoice_number, i.invoice_date, i.amoun t, i.invoice_style, i.plan_name -> Nested Loop Left Join (cost=485541.78..485551.50 rows=7 width=736) -> HashAggregate (cost=485540.75..485540.77 rows=1 width=56) -> Nested Loop Left Join (cost=417895.29..485536.25 rows=200 width=56) -> Index Scan using eg_invoice_pkey on eg_invoice i (cost=0.00..3.01 rows=1 width= 48) Index Cond: (invoice_id = 5) -> GroupAggregate (cost=417895.29..485529.24 rows=200 width=316) Filter: (max("?column3?") = 5) -> Unique (cost=417895.29..448632.54 rows=614745 width=200) -> Sort (cost=417895.29..419432.15 rows=614745 width=200) Sort Key: order_id, cso_id, invoice_id, period_id, ref_id, order_t ype, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, order_from, order_to, hours, mileage, amount -> Append (cost=0.00..106638.70 rows=614745 width=200) -> Subquery Scan "*SELECT* 1" (cost=0.00..9621.64 rows=233 432 width=200) -> Seq Scan on eg_order (cost=0.00..7287.32 rows=233 432 width=200) -> Result (cost=77951.41..97017.06 rows=381313 width=16) -> Unique (cost=77951.41..97017.06 rows=381313 width =16) -> Sort (cost=77951.41..78904.69 rows=381313 w idth=16) Sort Key: order_id, cso_id, invoice_id, pe riod_id, ref_id, order_type, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, order_from, order_to, hour s, mileage, amount -> Append (cost=0.00..25112.52 rows=3813 13 width=16) -> Subquery Scan "*SELECT* 2" (cos t=0.00..11887.06 rows=146043 width=16) -> Seq Scan on eg_order_line (cost=0.00..10426.63 rows=146043 width=16) Filter: (order_line_type = 20) -> Subquery Scan "*SELECT* 3" (cos t=0.00..13225.46 rows=235270 width=16) -> Seq Scan on eg_order_line (cost=0.00..10872.76 rows=235270 width=16) Filter: (order_line_type <> 20) -> Bitmap Heap Scan on eg_invoice_tax tax (cost=1.03..10.65 rows=7 width=8) Recheck Cond: (invoice_id = 5) -> Bitmap Index Scan on ix2f10773c8edf278d (cost=0.00..1.03 rows=7 width=0) Index Cond: (invoice_id = 5) (31 rows) -- ---- Visit http://www.obviously.com/ --------------040701090906080406070000 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit I've got a legacy app with a hefty performance problem.  The basic problem is stupid design. It takes 10-15 seconds of CPU time to look up an invoice.
Basically it's trying to mash up extra columns on an otherwise simple query, and those extra columns are subtotals.  Simplified (this looks best in a fixed width font):

    SELECT  max(order_view.order_id),max(order_view.invoice_id) ,sum(order_view.mileage)
    FROM    (SELECT order_id,invoice_id, 0 as miles FROM eg_order
             UNION   
             SELECT order_id,0         ,      miles FROM eg_order_line)
             order_view GROUP BY order_view.order_id;

A select by order_id is fast.  The problem is the application uses "select * from view where invoice_id=x", and the second part of the UNION returns all possible rows in the database.  These get filtered out later, but at considerable performance hit.

Is there a way to get the "where invoice_id=x" into the subquery?  "select distinct order_id from eg_order where invoice_id=x" would do it.
I can't redesign the view, because it all goes into an object relational mapper that thinks it's a real table.

                   -Bryce Nesbitt


stage=# \d eg_invoice_summary_view
        View "public.eg_invoice_summary_view"
     Column     |          Type          | Modifiers
----------------+------------------------+-----------
 invoice_id     | integer                |
 cso_id         | integer                |
 period_id      | integer                |
 account_id     | integer                |
 invoice_number | character varying(192) |
 invoice_date   | date                   |
 amount         | numeric                |
 tax            | bigint                 |
 invoice_style  | integer                |
 plan_name      | character varying(128) |
View definition:
 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
   FROM ( SELECT i.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
           FROM eg_invoice i
      LEFT JOIN eg_order_summary_view o ON i.invoice_id = o.invoice_id
     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
   LEFT JOIN eg_invoice_tax tax ON i.invoice_id = tax.invoice_id
  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;

stage=# \d eg_order_summary_view
        View "public.eg_order_summary_view"
   Column   |           Type           | Modifiers
------------+--------------------------+-----------
 order_id   | integer                  |
 d          | "unknown"                |
 cso_id     | integer                  |
 invoice_id | integer                  |
 period_id  | integer                  |
 ref_id     | integer                  |
 order_type | integer                  |
 desc1      | text                     |
 desc2      | text                     |
 desc3      | text                     |
 desc4      | text                     |
 desc5      | text                     |
 desc6      | text                     |
 desc7      | text                     |
 desc8      | text                     |
 order_from | timestamp with time zone |
 order_to   | timestamp with time zone |
 hours      | double precision         |
 mileage    | double precision         |
 amount     | bigint                   |
View definition:
 SELECT order_view.order_id, 'D' AS d, max(order_view.cso_id) AS cso_id, max(order_view.invoice_id) AS invoice_id, 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
   FROM ( SELECT eg_order.order_id, eg_order.cso_id, eg_order.invoice_id, 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
           FROM eg_order
UNION
        ( SELECT eg_order_line.order_id, 0, 0, 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
           FROM eg_order_line
          WHERE eg_order_line.order_line_type = 20
UNION
         SELECT eg_order_line.order_id, 0, 0, 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 AS mileage, eg_order_line.amt_value
           FROM eg_order_line
          WHERE eg_order_line.order_line_type <> 20)) order_view
  GROUP BY order_view.order_id;

stage=# explain select * from eg_invoice_summary_view where invoice_id=5;
                                                                                                              
    QUERY PLAN                                                                                                
                  
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
-------------------
 GroupAggregate  (cost=485551.60..485551.80 rows=1 width=736)
   ->  Sort  (cost=485551.60..485551.61 rows=7 width=736)
         Sort Key: i.invoice_id, i.cso_id, i.period_id, i.account_id, i.invoice_number, i.invoice_date, i.amoun
t, i.invoice_style, i.plan_name
         ->  Nested Loop Left Join  (cost=485541.78..485551.50 rows=7 width=736)
               ->  HashAggregate  (cost=485540.75..485540.77 rows=1 width=56)
                     ->  Nested Loop Left Join  (cost=417895.29..485536.25 rows=200 width=56)
                           ->  Index Scan using eg_invoice_pkey on eg_invoice i  (cost=0.00..3.01 rows=1 width=
48)
                                 Index Cond: (invoice_id = 5)
                           ->  GroupAggregate  (cost=417895.29..485529.24 rows=200 width=316)
                                 Filter: (max("?column3?") = 5)
                                 ->  Unique  (cost=417895.29..448632.54 rows=614745 width=200)
                                       ->  Sort  (cost=417895.29..419432.15 rows=614745 width=200)
                                             Sort Key: order_id, cso_id, invoice_id, period_id, ref_id, order_t
ype, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, order_from, order_to, hours, mileage, amount
                                             ->  Append  (cost=0.00..106638.70 rows=614745 width=200)
                                                   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..9621.64 rows=233
432 width=200)
                                                         ->  Seq Scan on eg_order  (cost=0.00..7287.32 rows=233
432 width=200)
                                                   ->  Result  (cost=77951.41..97017.06 rows=381313 width=16)
                                                         ->  Unique  (cost=77951.41..97017.06 rows=381313 width
=16)
                                                               ->  Sort  (cost=77951.41..78904.69 rows=381313 w
idth=16)
                                                                     Sort Key: order_id, cso_id, invoice_id, pe
riod_id, ref_id, order_type, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, order_from, order_to, hour
s, mileage, amount
                                                                     ->  Append  (cost=0.00..25112.52 rows=3813
13 width=16)
                                                                           ->  Subquery Scan "*SELECT* 2"  (cos
t=0.00..11887.06 rows=146043 width=16)
                                                                                 ->  Seq Scan on eg_order_line
 (cost=0.00..10426.63 rows=146043 width=16)
                                                                                       Filter: (order_line_type
 = 20)
                                                                           ->  Subquery Scan "*SELECT* 3"  (cos
t=0.00..13225.46 rows=235270 width=16)
                                                                                 ->  Seq Scan on eg_order_line
 (cost=0.00..10872.76 rows=235270 width=16)
                                                                                       Filter: (order_line_type
 <> 20)
               ->  Bitmap Heap Scan on eg_invoice_tax tax  (cost=1.03..10.65 rows=7 width=8)
                     Recheck Cond: (invoice_id = 5)
                     ->  Bitmap Index Scan on ix2f10773c8edf278d  (cost=0.00..1.03 rows=7 width=0)
                           Index Cond: (invoice_id = 5)
(31 rows)



-- 
----
Visit http://www.obviously.com/
--------------040701090906080406070000--