From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Thu, 26 Jul 2007 00:30:00 -0700
Message-ID: <46A84D78.8020200@obviously.com> (raw)
In-Reply-To: <2385.1185127507@sss.pgh.pa.us>
References: <46A2CABF.3080108@obviously.com>
<f7v6na$2gc$1@sea.gmane.org>
<2385.1185127507@sss.pgh.pa.us>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce1@obviously.com
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <46A84D78.8020200@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox