-- 基礎(chǔ)形式（僅預(yù)估）
EXPLAIN SELECT * FROM products WHERE price > 100;

-- 實(shí)際執(zhí)行+詳細(xì)統(tǒng)計(jì)（生產(chǎn)環(huán)境必備）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING) 
SELECT p.name, o.order_date 
FROM products p JOIN orders o ON p.id = o.product_id 
WHERE p.category = 'Electronics';

QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_products_price on products  (cost=0.29..8.31 rows=1 width=204)
  Index Cond: (price > 100.00)
  Buffers: shared hit=5 read=2
  Actual Time=0.045..0.047 rows=1 loops=1

-- 觸發(fā)場(chǎng)景：無(wú)合適索引或數(shù)據(jù)量小
EXPLAIN SELECT * FROM users WHERE registration_date > '2025-01-01';

-- 典型高效場(chǎng)景
EXPLAIN SELECT * FROM orders WHERE order_id = 10086;

-- 復(fù)合條件查詢(xún)的優(yōu)化方案
EXPLAIN SELECT * FROM products 
WHERE price > 100 AND category = 'Electronics';

-- 大表連接的首選方案
EXPLAIN SELECT o.order_id, c.name 
FROM orders o JOIN customers c ON o.customer_id = c.id;

-- 適合小表驅(qū)動(dòng)大表的場(chǎng)景
EXPLAIN SELECT * FROM order_items oi 
WHERE oi.order_id IN (SELECT id FROM orders WHERE status = 'completed');

SELECT c.name, COUNT(o.id) as order_count
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.region = 'Asia'
GROUP BY c.name
ORDER BY order_count DESC
LIMIT 10;

Hash Join (cost=12500.30..15000.45 rows=500 width=32)
  ->  Seq Scan on customers (cost=0.00..1200.50 rows=50000 width=32)
        Filter: (region = 'Asia'::text)
  ->  Hash (cost=10000.20..10000.20 rows=100000 width=8)
        ->  Seq Scan on orders (cost=0.00..8000.20 rows=100000 width=8)

CREATE INDEX idx_customers_region_asia ON customers (id) 
WHERE region = 'Asia';

SELECT c.name, COALESCE(o.cnt, 0) as order_count
FROM (SELECT id, name FROM customers WHERE region = 'Asia') c
LEFT JOIN (
  SELECT customer_id, COUNT(*) as cnt 
  FROM orders 
  GROUP BY customer_id
) o ON c.id = o.customer_id
ORDER BY order_count DESC
LIMIT 10;

Nested Loop Left Join (cost=0.29..125.45 rows=10 width=32)
  ->  Index Scan using idx_customers_region_asia on customers c (cost=0.29..8.30 rows=1 width=32)
  ->  HashAggregate (cost=100.00..110.00 rows=1000 width=12)
        Group Key: o.customer_id
        ->  Seq Scan on orders o (cost=0.00..80.00 rows=10000 width=8)

SELECT date_trunc('day', order_date) as day, 
       SUM(amount) as total_sales
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY day
ORDER BY day;

SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 10;
SET parallel_tuple_cost = 0.1;

CREATE INDEX idx_orders_date_brin ON orders USING BRIN (order_date);

Gather Merge (cost=125000.00..135000.00 rows=365 width=16)
  Workers Planned: 4
  ->  Sort (cost=120000.00..120090.00 rows=365 width=16)
        Sort Key: (date_trunc('day'::text, order_date))
        ->  Parallel HashAggregate (cost=110000.00..115000.00 rows=365 width=16)
              Group Key: (date_trunc('day'::text, order_date))
              ->  Parallel Index Scan using idx_orders_date_brin on orders 
                   (cost=0.00..100000.00 rows=1000000 width=8)

-- 定期更新統(tǒng)計(jì)信息
ANALYZE VERBOSE customers, orders;

-- 調(diào)整自動(dòng)統(tǒng)計(jì)收集閾值
ALTER TABLE orders SET (autovacuum_analyze_threshold = 5000);

-- 根據(jù)硬件調(diào)整I/O成本（SSD可降低random_page_cost）
SHOW random_page_cost;  -- 默認(rèn)4.0
SET random_page_cost = 1.1;  -- SSD環(huán)境推薦值

-- 調(diào)整工作內(nèi)存（影響哈希連接/排序性能）
SHOW work_mem;
SET work_mem = '64MB';  -- 復(fù)雜查詢(xún)建議值