PostgreSQL: PostgreSQL性能优化实战

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=72) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (user_id = 1001)
  Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.032 ms

-- No index on status, planner chooses Seq Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM orders WHERE status = 'pending';

Aggregate (actual time=45.123..45.124 rows=1 loops=1)
  -> Seq Scan on orders (actual time=0.012..42.890 rows=50000 loops=1)
       Filter: (status = 'pending'::text)
       Rows Removed by Filter: 950000

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- High selectivity: planner uses Index Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders (actual time=0.015..0.018 rows=3 loops=1)
  Index Cond: (user_id = 1001)

-- Moderate selectivity: planner chooses Bitmap
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id BETWEEN 1000 AND 1100;

Bitmap Heap Scan on orders (actual time=0.523..2.145 rows=523 loops=1)
  Recheck Cond: (user_id >= 1000 AND user_id <= 1100)
  -> Bitmap Index Scan on idx_orders_user_id (actual time=0.412..0.412 rows=523 loops=1)
       Index Cond: (user_id >= 1000 AND user_id <= 1100)

-- Small outer table + indexed inner = Nest Loop
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1001;

-- Large outer + unsorted inner = Hash Join
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date > '2024-01-01';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Slow query: Seq Scan on large table
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan, cost=0.00..15432.00, actual time=120ms

-- Add index
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Re-analyze for accurate statistics
ANALYZE orders;

-- Same query now uses Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Index Scan, cost=0.42..890.00, actual time=2ms

CREATE TABLE

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 by total time
SELECT left(query, 80) AS query_preview,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       rows,
       round((100.0 * shared_blks_hit /
           nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

-- Queries with most disk reads
SELECT left(query, 80) AS query_preview,
       shared_blks_read AS disk_reads,
       shared_blks_hit  AS cache_hits,
       calls,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

# pgbouncer.ini
[databases]
shop_db = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

# postgresql.conf tuning for 16GB RAM server
shared_buffers = 4GB           # 25% of RAM
work_mem = 64MB                # per-sort-operation memory
effective_cache_size = 12GB    # 75% of RAM
maintenance_work_mem = 1GB     # for VACUUM, CREATE INDEX
effective_io_concurrency = 200 # SSD; 2 for HDD
random_page_cost = 1.1         # SSD; 4.0 for HDD

-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

-- Check at runtime (no restart needed for some)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

-- Must-restart parameters
SELECT name, setting, boot_val, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

ALTER TABLE

-- Check dead tuple ratio
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- For high-write tables, lower scale factor for more frequent vacuum
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = '1ms',
    autovacuum_vacuum_cost_limit = 1000
);

-- For orders with 10M rows, vacuum triggers at:
-- 10M × 0.05 = 500K dead tuples (vs default 2M)

-- SQL 语句执行成功

-- PG 12+: track vacuum progress
SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum;

 count 
-------
     5
(1 row)

-- Enable parallel query (PG 10+)
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET parallel_tuple_cost = 0.001;
SET min_parallel_table_scan_size = '8MB';

-- Verify parallel plan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*), category
FROM products
GROUP BY category;

Finalize Aggregate (actual time=12.3..12.4 rows=5 loops=1)
  -> Gather (actual time=12.1..12.3 rows=15 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       -> Partial Aggregate (actual time=8.5..8.6 rows=5 loops=3)
            -> Parallel Seq Scan on products (actual time=0.2..6.1 rows=33333 loops=3)

SELECT relname AS table_name,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       round(100.0 * idx_scan / nullif(idx_scan + seq_scan, 0), 2) AS idx_scan_pct,
       n_live_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Anti-pattern 1: OR condition preventing index use
-- BAD
SELECT * FROM orders WHERE user_id = 1001 OR status = 'pending';
-- GOOD: use UNION ALL
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1001;

-- Anti-pattern 2: function wrapping indexed column
-- BAD
SELECT * FROM orders WHERE lower(status) = 'pending';
-- GOOD
SELECT * FROM orders WHERE status = lower('PENDING');

-- Anti-pattern 3: LIKE with leading wildcard
-- BAD
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD: pg_trgm or full-text search
SELECT * FROM products WHERE name % 'phone';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Full performance optimization workflow for Charlie's e-commerce system

-- Step 1: Identify slow queries
SELECT left(query, 60) AS q, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- Step 2: Analyze worst query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- Step 3: Add missing indexes
CREATE INDEX idx_orders_date_status ON orders (order_date, status);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- Step 4: Update statistics
ANALYZE orders;
ANALYZE order_items;
ANALYZE products;

-- Step 5: Tune autovacuum for high-write tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

-- Step 6: Increase work_mem for complex sorts
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

-- Step 7: Verify improvement
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- Step 8: Monitor table health
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'products')
ORDER BY n_dead_tup DESC;