Database Reference
In-Depth Information
WHERE sale_time >= '2009-01-01'
AND sale_time < '2010-01-01'
AND sploc.id != whloc.id
GROUP BY 1,2
)
SELECT shop.sp_name AS shop_name,
q1_nloc_profit.profit as q1_profit,
q2_nloc_profit.profit as q2_profit,
q3_nloc_profit.profit as q3_profit,
q4_nloc_profit.profit as q4_profit,
year_nloc_profit.profit as year_profit
FROM (SELECT * FROM salespoint ORDER BY sp_name) AS shop
LEFT JOIN nlqp AS q1_nloc_profit
ON shop.id = Q1_NLOC_PROFIT.spoint_id
AND q1_nloc_profit.sale_quarter = 1
LEFT JOIN nlqp AS q2_nloc_profit
ON shop.id = Q2_NLOC_PROFIT.spoint_id
AND q2_nloc_profit.sale_quarter = 2
LEFT JOIN nlqp AS q3_nloc_profit
ON shop.id = Q3_NLOC_PROFIT.spoint_id
AND q3_nloc_profit.sale_quarter = 3
LEFT JOIN nlqp AS q4_nloc_profit
ON shop.id = Q4_NLOC_PROFIT.spoint_id
AND q4_nloc_profit.sale_quarter = 4
LEFT JOIN (
SELECT spoint_id, sum(profit) AS profit
FROM nlqp GROUP BY 1
) AS year_nloc_profit
ON shop.id = year_nloc_profit.spoint_id
ORDER BY 1
;
Using temporary tables for parts of the query
PostgreSQL itself can choose to materialize parts of the query during the query optimization
phase, but sometimes it fails to make the best choice for the query plan either due to
insufficient statistics, or as it can happen for large query plans, where genetic query
optimization (GEQO) is used, it may have just overlooked some possible query plans.
If you think that materializing (preparing separately) some parts of the query is a good
idea, you can do it using a temporary table, simply by running
CREATE
TEMPORARY
TABLE
mytemptable01
AS
<the part of the query you want to materialize>, and then using
mytemptable01 in the main query instead of the part materialized. You can even create
indexes on the temp table for PostgreSQL to use in the main query.
