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;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
