Group9_JoinPerformance.sql

------------------------------------------------------------------
     -- PART 3: JOIN TECHNIQUES AND QUERY OPTIMIZATION
------------------------------------------------------------------

-- 1. Nested Loop Join
-- =====================================================================
-- Forces PostgreSQL to use nested loop join for testing
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
SET enable_mergejoin = ON;

EXPLAIN ANALYZE
SELECT f.transaction_id, c.customer_id, m.merchant_city, f.amount
FROM dw.fact_transactions f
JOIN dw.dim_customer c ON f.customer_key = c.customer_key
JOIN dw.dim_merchant m ON f.merchant_key = m.merchant_key
WHERE f.amount > 500;



-- 2. Sort-Merge Join
-- =====================================================================
-- Forces PostgreSQL to use merge join for testing
SET enable_hashjoin = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;

EXPLAIN ANALYZE
SELECT f.transaction_id, cd.card_brand, f.amount
FROM dw.fact_transactions f
JOIN dw.dim_card cd ON f.card_key = cd.card_key
WHERE f.fraud_label = TRUE
ORDER BY cd.card_brand, f.amount;


-- 3. Hash Join
-- =====================================================================
-- Forces PostgreSQL to use hash join
SET enable_mergejoin = OFF;
SET enable_nestloop = OFF;
SET enable_hashjoin = ON;

EXPLAIN ANALYZE
SELECT 
    m.merchant_state,
    SUM(f.amount) AS total_amount,
    COUNT(*) AS num_transactions
FROM dw.fact_transactions f
JOIN dw.dim_merchant m 
    ON f.merchant_key = m.merchant_key
GROUP BY m.merchant_state
ORDER BY total_amount DESC;



------------------------------------------------------------------
-- DSS vs OLTP query comparison
------------------------------------------------------------------

-- DSS Query (Decision Support System)
-- Long-running aggregation example: monthly revenue by state
EXPLAIN ANALYZE
SELECT d.year, d.month, m.merchant_state, SUM(f.amount) AS monthly_total
FROM dw.fact_transactions f
JOIN dw.dim_date d ON f.date_key = d.date_key
JOIN dw.dim_merchant m ON f.merchant_key = m.merchant_key
GROUP BY d.year, d.month, m.merchant_state
ORDER BY d.year, d.month, m.merchant_state;


-- OLTP Query (Online Transaction Processing)
-- Simple lookup: find one transaction by ID
EXPLAIN ANALYZE
SELECT f.transaction_id, c.customer_id, f.amount, f.fraud_label
FROM dw.fact_transactions f
JOIN dw.dim_customer c ON f.customer_key = c.customer_key
WHERE f.transaction_id = 123456;

-- Observations:
-- 1. DSS queries have higher cost, scan more rows, may trigger full table scans.
-- 2. OLTP queries are faster due to indexes (primary keys, foreign keys), small row access.
-- 3. DSS often benefits from pre-aggregated tables (MOLAP) or proper indexes on join keys.
-- 4. OLTP queries are optimized for quick reads/writes with indexed lookups.