Query Planning: Optimalkan Performa Query 🚀

-- Menganalisis rencana eksekusi query
EXPLAIN 
SELECT o.*, c.name as customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
AND o.order_date >= '2024-01-01';

-- Rencana eksekusi yang dihasilkan:
                                    QUERY PLAN
-----------------------------------------------------------------------------------
Hash Join  (cost=33.38..58.54 rows=150 width=545)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..25.00 rows=150 width=325)
        Filter: ((status = 'completed'::text) AND (order_date >= '2024-01-01'::date))
  ->  Hash  (cost=21.30..21.30 rows=965 width=220)
        ->  Seq Scan on customers c  (cost=0.00..21.30 rows=965 width=220)

-- Menganalisis dan mengeksekusi query
EXPLAIN ANALYZE
SELECT 
    p.category,
    COUNT(*) as total_orders,
    AVG(o.amount) as avg_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.category;

-- Hasil analisis eksekusi:
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=97.82..99.82 rows=200 width=72) (actual time=0.652..0.654 rows=3 loops=1)
  Group Key: p.category
  ->  Hash Join  (cost=43.32..89.32 rows=850 width=40) (actual time=0.124..0.567 rows=845 loops=1)
        Hash Cond: (oi.product_id = p.id)
        ->  Hash Join  (cost=24.35..61.35 rows=850 width=36) (actual time=0.089..0.398 rows=845 loops=1)
              Hash Cond: (oi.order_id = o.id)
              ->  Seq Scan on order_items oi  (cost=0.00..28.50 rows=850 width=16) (actual time=0.008..0.156 rows=845 loops=1)
              ->  Hash  (cost=19.35..19.35 rows=400 width=20) (actual time=0.069..0.069 rows=400 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 24kB
                    ->  Seq Scan on orders o  (cost=0.00..19.35 rows=400 width=20) (actual time=0.005..0.039 rows=400 loops=1)
                          Filter: (order_date >= '2024-01-01'::date)
        ->  Hash  (cost=12.40..12.40 rows=525 width=36) (actual time=0.024..0.024 rows=525 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 28kB
              ->  Seq Scan on products p  (cost=0.00..12.40 rows=525 width=36) (actual time=0.003..0.013 rows=525 loops=1)
Planning Time: 0.195 ms
Execution Time: 0.688 ms

-- Data hasil query:
category    | total_orders | avg_amount
----------- | ------------ | ----------
Electronics | 250          | 1200.50
Gadgets     | 350          | 450.75
Appliances  | 150          | 300.25

-- Melihat statistik query yang sedang berjalan
SELECT 
    pid,
    query,
    state,
    wait_event_type,
    EXTRACT(EPOCH FROM (now() - query_start))::INT as duration_seconds
FROM pg_stat_activity
WHERE state != 'idle';

-- Proses query yang aktif:
pid  | query                          | state    | wait_event_type | duration_seconds
---- | ------------------------------ | -------- | --------------- | ----------------
1234 | SELECT * FROM large_table ... | active   | IO              | 15
5678 | UPDATE users SET ...          | active   | Client          | 5
9012 | INSERT INTO logs ...          | active   | CPU             | 2

-- Melihat statistik tabel
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_scan DESC
LIMIT 5;

-- Statistik penggunaan tabel:
schemaname | relname   | seq_scan | seq_tup_read | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
---------- | --------- | -------- | ------------ | -------- | --------- | --------- | ---------
public     | orders    | 15000    | 1500000      | 50000    | 10000     | 5000      | 100
public     | users     | 12000    | 1200000      | 45000    | 1000      | 2000      | 50
public     | products  | 10000    | 500000       | 30000    | 500       | 1000      | 25

-- Buat indeks B-tree
CREATE INDEX idx_products_name 
ON products (name);

-- Buat indeks unik
CREATE UNIQUE INDEX idx_users_email 
ON users (email);

-- Buat indeks multi-kolom
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date);

-- Buat indeks parsial
CREATE INDEX idx_active_products 
ON products (name) 
WHERE active = true;

-- Indeks ekspresi
CREATE INDEX idx_lower_email 
ON users (LOWER(email));

-- Include columns
CREATE INDEX idx_orders_customer 
ON orders (customer_id)
INCLUDE (status, total_amount);

-- Indeks BRIN untuk data berurutan
CREATE INDEX idx_logs_timestamp 
ON log_entries 
USING BRIN (created_at);

-- Indeks GiST untuk data geometri
CREATE INDEX idx_locations 
ON stores 
USING GIST (location);

-- Optimasi urutan JOIN
EXPLAIN ANALYZE
SELECT 
    c.name as nama,
    p.product_name as nama_produk,
    o.order_date as tanggal_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE c.country = 'ID'
AND o.order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Gunakan subquery secara efektif
EXPLAIN ANALYZE
SELECT 
    product_name as nama_produk,
    price as harga,
    (
        SELECT AVG(price)
        FROM products p2
        WHERE p2.category_id = p1.category_id
    ) as rata_rata_harga_kategori
FROM products p1
WHERE price > 100;

-- Gunakan EXISTS untuk efisiensi
EXPLAIN ANALYZE
SELECT c.name as nama
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
    AND o.status = 'completed'
);

-- Optimasi klausa IN
EXPLAIN ANALYZE
SELECT p.product_name as nama_produk, p.price as harga
FROM products p
WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE active = true
);

-- Gunakan CTE untuk query kompleks
EXPLAIN ANALYZE
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as bulan,
        SUM(total_amount) as total_penjualan
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    bulan,
    total_penjualan,
    LAG(total_penjualan) OVER (ORDER BY bulan) as penjualan_bulan_lalu,
    ROUND(
        (total_penjualan - LAG(total_penjualan) OVER (ORDER BY bulan)) * 100.0 /
        NULLIF(LAG(total_penjualan) OVER (ORDER BY bulan), 0),
        2
    ) as persentase_pertumbuhan
FROM monthly_sales
ORDER BY bulan DESC;

-- Statistik tabel
SELECT 
    schemaname as nama_schema,
    relname as nama_tabel,
    seq_scan as jumlah_seq_scan,
    seq_tup_read as jumlah_baris_dibaca,
    idx_scan as jumlah_idx_scan,
    n_tup_ins as jumlah_insert,
    n_tup_upd as jumlah_update,
    n_tup_del as jumlah_delete,
    n_live_tup as jumlah_baris_aktif,
    n_dead_tup as jumlah_baris_mati
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

-- Statistik penggunaan indeks
SELECT 
    schemaname as nama_schema,
    tablename as nama_tabel,
    indexname as nama_indeks,
    idx_scan as jumlah_scan,
    idx_tup_read as jumlah_baris_dibaca,
    idx_tup_fetch as jumlah_baris_diambil
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_scan DESC;

-- Analisis query lambat
SELECT 
    substring(query, 1, 50) as preview_query,
    calls as jumlah_panggilan,
    total_time as total_waktu,
    mean_time as rata_rata_waktu,
    rows as jumlah_baris
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Statistik koneksi
SELECT 
    datname as nama_database,
    numbackends as jumlah_koneksi,
    xact_commit as commit_sukses,
    xact_rollback as rollback,
    blks_read as blok_dibaca,
    blks_hit as blok_cache_hit,
    tup_returned as baris_dikembalikan,
    tup_fetched as baris_diambil,
    tup_inserted as baris_diinsert,
    tup_updated as baris_diupdate,
    tup_deleted as baris_dihapus
FROM pg_stat_database
WHERE datname = CURRENT_DATABASE();