######## 0. 실습 환경 $ docker run -d --init --name pglab10 --hostname pglab10 pg-internals:rel18-lab sleep infinity 7a738452e35a029c815ce441f26d8e8729333d02955590d4cbad6ca5e5ccf293 [exit=0] $ postgres --version $ initdb -D $PGDATA > /home/postgres/initdb.log 2>&1 && echo "initdb ok" postgres (PostgreSQL) 18.6 initdb ok [exit=0] $ pg_ctl -D $PGDATA -l /home/postgres/server.log start $ psql -X -q <<'SQL' $ CREATE TABLE customers (id int PRIMARY KEY, name text); $ INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 1000) g; $ CREATE TABLE orders ( $ id int PRIMARY KEY, customer_id int, status text, amount int, city text, country text); $ INSERT INTO orders $ SELECT g, g % 1000 + 1, $ CASE WHEN g % 1000 < 800 THEN 'delivered' WHEN g % 1000 < 950 THEN 'shipped' $ WHEN g % 1000 < 999 THEN 'cancelled' ELSE 'returned' END, $ (g * 7919) % 1000, $ (ARRAY['Seoul','Busan','Tokyo','Osaka','Paris','Lyon','Berlin','Munich','Rome','Milan'])[g % 10 + 1], $ (ARRAY['KR','KR','JP','JP','FR','FR','DE','DE','IT','IT'])[g % 10 + 1] $ FROM generate_series(1, 100000) g; $ CREATE INDEX orders_customer_idx ON orders (customer_id); $ CREATE INDEX orders_amount_idx ON orders (amount); $ ANALYZE; $ SQL waiting for server to start.... done server started [exit=0] ######## 1. 단계마다 걸린 시간: 파서, 분석, 리라이터, 플래너, 실행기 $ PGOPTIONS="-c client_min_messages=log -c log_parser_stats=on -c log_planner_stats=on -c log_executor_stats=on" \ $ psql -X -c "SELECT status, count(*) FROM orders WHERE amount < 100 GROUP BY status" 2>&1 | grep -E "^LOG:|elapsed|^ +status +\||^-+\+|^ [a-z]+ +\|" LOG: PARSER STATISTICS ! 0.000028 s user, 0.000000 s system, 0.000028 s elapsed LOG: PARSE ANALYSIS STATISTICS ! 0.000239 s user, 0.000000 s system, 0.000239 s elapsed LOG: REWRITER STATISTICS ! 0.000005 s user, 0.000000 s system, 0.000005 s elapsed LOG: PLANNER STATISTICS ! 0.000200 s user, 0.000000 s system, 0.000200 s elapsed LOG: EXECUTOR STATISTICS ! 0.001856 s user, 0.000000 s system, 0.001856 s elapsed status | count -----------+------- returned | 100 cancelled | 400 shipped | 1400 delivered | 8100 [exit=0] ######## 2. 오류가 나는 단계로 보는 파서와 분석기의 차이 $ psql -X -c "SELEC * FROM orders" $ psql -X -c "SELECT * FROM order_typo" $ psql -X -c "SELECT nosuchcol FROM orders" ERROR: syntax error at or near "SELEC" LINE 1: SELEC * FROM orders ^ ERROR: relation "order_typo" does not exist LINE 1: SELECT * FROM order_typo ^ ERROR: column "nosuchcol" does not exist LINE 1: SELECT nosuchcol FROM orders ^ [exit=1] ######## 3. 내부 트리: Query 트리와 계획 트리(PlannedStmt) $ psql -X -c "SET client_min_messages = log" -c "SET debug_print_rewritten = on" -c "SELECT status, count(*) FROM orders WHERE amount < 100 GROUP BY status" 2>&1 | grep -oE "\{[A-Z_]+" | awk '!seen[$0]++' | tr '\n' ' '; echo $ psql -X -c "SET client_min_messages = log" -c "SET debug_print_plan = on" -c "SELECT status, count(*) FROM orders WHERE amount < 100 GROUP BY status" 2>&1 | grep -oE "\{[A-Z_]+" | awk '!seen[$0]++' | tr '\n' ' '; echo {QUERY {RANGETBLENTRY {ALIAS {VAR {RTEPERMISSIONINFO {FROMEXPR {RANGETBLREF {OPEXPR {CONST {TARGETENTRY {AGGREF {SORTGROUPCLAUSE {PLANNEDSTMT {AGG {TARGETENTRY {VAR {AGGREF {BITMAPHEAPSCAN {BITMAPINDEXSCAN {OPEXPR {CONST {RANGETBLENTRY {ALIAS {RTEPERMISSIONINFO [exit=0] ######## 4. 리라이터: 뷰는 원래 테이블로 풀린다 $ psql -X -q -c "CREATE VIEW big_orders AS SELECT id, customer_id, amount FROM orders WHERE amount >= 990" $ psql -X -c "EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id = 7" QUERY PLAN --------------------------------------------------------- Bitmap Heap Scan on orders Recheck Cond: ((customer_id = 7) AND (amount >= 990)) -> BitmapAnd -> Bitmap Index Scan on orders_customer_idx Index Cond: (customer_id = 7) -> Bitmap Index Scan on orders_amount_idx Index Cond: (amount >= 990) (7 rows) [exit=0] ######## 5. 통계 정보: pg_stats $ psql -X -x -c "SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation FROM pg_stats WHERE tablename = 'orders' AND attname = 'status'" $ psql -X -x -c "SELECT attname, n_distinct, array_length(most_common_vals::text::int[], 1) AS mcv_count, (SELECT sum(f) FROM unnest(most_common_freqs) f) AS mcv_total_freq, array_length(histogram_bounds, 1) AS histogram_len, (histogram_bounds::text::int[])[1:6] AS histogram_head, correlation FROM pg_stats WHERE tablename = 'orders' AND attname = 'amount'" $ psql -X -c "SELECT relname, relpages, reltuples FROM pg_class WHERE relname IN ('orders', 'customers') ORDER BY relname" -[ RECORD 1 ]-----+--------------------------------------- attname | status null_frac | 0 n_distinct | 4 most_common_vals | {delivered,shipped,cancelled,returned} most_common_freqs | {0.7984667,0.1502,0.050333332,0.001} correlation | 0.66393787 -[ RECORD 1 ]--+------------------- attname | amount n_distinct | 1000 mcv_count | 3 mcv_total_freq | 0.0043 histogram_len | 101 histogram_head | {0,10,20,31,41,50} correlation | 0.0046508736 relname | relpages | reltuples -----------+----------+----------- customers | 7 | 1000 orders | 805 | 100000 (2 rows) [exit=0] ######## 6. 비용 모델: Seq Scan의 비용을 직접 계산해 보기 $ psql -X -c "SHOW seq_page_cost" -c "SHOW random_page_cost" -c "SHOW cpu_tuple_cost" -c "SHOW cpu_operator_cost" $ psql -X -c "EXPLAIN SELECT * FROM orders" $ psql -X -c "EXPLAIN SELECT * FROM orders WHERE status = 'returned'" $ psql -X -c "SELECT relpages * 1.0 + reltuples * 0.01 AS seqscan_cost, relpages * 1.0 + reltuples * 0.01 + reltuples * 0.0025 AS with_filter_cost FROM pg_class WHERE relname = 'orders'" seq_page_cost --------------- 1 (1 row) random_page_cost ------------------ 4 (1 row) cpu_tuple_cost ---------------- 0.01 (1 row) cpu_operator_cost ------------------- 0.0025 (1 row) QUERY PLAN --------------------------------------------------------------- Seq Scan on orders (cost=0.00..1805.00 rows=100000 width=30) (1 row) QUERY PLAN ------------------------------------------------------------ Seq Scan on orders (cost=0.00..2055.00 rows=100 width=30) Filter: (status = 'returned'::text) (2 rows) seqscan_cost | with_filter_cost --------------+------------------ 1805 | 2055 (1 row) [exit=0] ######## 7. 추정과 실제: MCV와 히스토그램 $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'shipped'" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'returned'" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE amount < 30" QUERY PLAN --------------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2055.00 rows=15020 width=30) (actual rows=15000.00 loops=1) Filter: (status = 'shipped'::text) Rows Removed by Filter: 85000 Planning Time: 0.142 ms Execution Time: 3.255 ms (5 rows) QUERY PLAN ----------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2055.00 rows=100 width=30) (actual rows=100.00 loops=1) Filter: (status = 'returned'::text) Rows Removed by Filter: 99900 Planning Time: 0.126 ms Execution Time: 2.829 ms (5 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on orders (cost=35.08..876.83 rows=2940 width=30) (actual rows=3000.00 loops=1) Recheck Cond: (amount < 30) Heap Blocks: exact=805 -> Bitmap Index Scan on orders_amount_idx (cost=0.00..34.34 rows=2940 width=0) (actual rows=3000.00 loops=1) Index Cond: (amount < 30) Index Searches: 1 Planning Time: 0.131 ms Execution Time: 0.718 ms (8 rows) [exit=0] ######## 8. 같은 쿼리, 다른 계획: 선택도에 따라 스캔 방식이 바뀐다 $ psql -X -c "EXPLAIN SELECT * FROM orders WHERE id = 42" $ psql -X -c "EXPLAIN SELECT * FROM orders WHERE amount < 30" $ psql -X -c "EXPLAIN SELECT * FROM orders WHERE amount < 800" QUERY PLAN --------------------------------------------------------------------------- Index Scan using orders_pkey on orders (cost=0.29..8.31 rows=1 width=30) Index Cond: (id = 42) (2 rows) QUERY PLAN ------------------------------------------------------------------------------------ Bitmap Heap Scan on orders (cost=35.08..876.83 rows=2940 width=30) Recheck Cond: (amount < 30) -> Bitmap Index Scan on orders_amount_idx (cost=0.00..34.34 rows=2940 width=0) Index Cond: (amount < 30) (4 rows) QUERY PLAN -------------------------------------------------------------- Seq Scan on orders (cost=0.00..2055.00 rows=79887 width=30) Filter: (amount < 800) (2 rows) [exit=0] ######## 9. 조인 방식: Nested Loop, Hash Join, Merge Join $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, COSTS OFF) SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 42" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, COSTS OFF) SELECT c.name, sum(o.amount) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.name" $ psql -X -c "SET enable_hashjoin = off" -c "SET enable_nestloop = off" -c "EXPLAIN (COSTS OFF) SELECT c.name, sum(o.amount) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.name" QUERY PLAN ----------------------------------------------------------------------------------- Nested Loop (actual rows=100.00 loops=1) -> Index Scan using customers_pkey on customers c (actual rows=1.00 loops=1) Index Cond: (id = 42) Index Searches: 1 -> Bitmap Heap Scan on orders o (actual rows=100.00 loops=1) Recheck Cond: (customer_id = 42) Heap Blocks: exact=100 -> Bitmap Index Scan on orders_customer_idx (actual rows=100.00 loops=1) Index Cond: (customer_id = 42) Index Searches: 1 Planning Time: 0.198 ms Execution Time: 0.326 ms (12 rows) QUERY PLAN ------------------------------------------------------------------------- HashAggregate (actual rows=1000.00 loops=1) Group Key: c.name Batches: 1 Memory Usage: 121kB -> Hash Join (actual rows=100000.00 loops=1) Hash Cond: (o.customer_id = c.id) -> Seq Scan on orders o (actual rows=100000.00 loops=1) -> Hash (actual rows=1000.00 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 59kB -> Seq Scan on customers c (actual rows=1000.00 loops=1) Planning Time: 0.278 ms Execution Time: 13.215 ms (11 rows) SET SET QUERY PLAN -------------------------------------------------------------- HashAggregate Group Key: c.name -> Merge Join Merge Cond: (c.id = o.customer_id) -> Index Scan using customers_pkey on customers c -> Index Scan using orders_customer_idx on orders o (6 rows) [exit=0] ######## 10. 실행기는 필요한 만큼만 당겨 온다 $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, COSTS OFF) SELECT * FROM orders LIMIT 5" QUERY PLAN ----------------------------------------------------- Limit (actual rows=5.00 loops=1) -> Seq Scan on orders (actual rows=5.00 loops=1) Planning Time: 0.131 ms Execution Time: 0.012 ms (4 rows) [exit=0] ######## 11. 통계가 오래되면: 분포가 바뀐 뒤의 추정 $ psql -X -q -c "ALTER TABLE orders SET (autovacuum_enabled = off)" $ psql -X -q -c "UPDATE orders SET status = 'returned' WHERE id % 10 = 0" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'returned'" $ psql -X -q -c "ANALYZE orders" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'returned'" QUERY PLAN ------------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2266.89 rows=110 width=30) (actual rows=10100.00 loops=1) Filter: (status = 'returned'::text) Rows Removed by Filter: 89900 Planning Time: 0.126 ms Execution Time: 3.744 ms (5 rows) QUERY PLAN --------------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2138.00 rows=10143 width=30) (actual rows=10100.00 loops=1) Filter: (status = 'returned'::text) Rows Removed by Filter: 89900 Planning Time: 0.120 ms Execution Time: 2.631 ms (5 rows) [exit=0] ######## 12. 서로 관련된 컬럼: 확장 통계 $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE city = 'Seoul' AND country = 'KR'" $ psql -X -q -c "CREATE STATISTICS orders_city_country (dependencies) ON city, country FROM orders" -c "ANALYZE orders" $ psql -X -c "SELECT statistics_name, dependencies FROM pg_stats_ext WHERE statistics_name = 'orders_city_country'" $ psql -X -c "EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE city = 'Seoul' AND country = 'KR'" QUERY PLAN -------------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2388.00 rows=2014 width=30) (actual rows=10000.00 loops=1) Filter: ((city = 'Seoul'::text) AND (country = 'KR'::text)) Rows Removed by Filter: 90000 Planning Time: 0.144 ms Execution Time: 3.971 ms (5 rows) statistics_name | dependencies ---------------------+---------------------- orders_city_country | {"5 => 6": 1.000000} (1 row) QUERY PLAN -------------------------------------------------------------------------------------------- Seq Scan on orders (cost=0.00..2388.00 rows=9950 width=29) (actual rows=10000.00 loops=1) Filter: ((city = 'Seoul'::text) AND (country = 'KR'::text)) Rows Removed by Filter: 90000 Planning Time: 0.140 ms Execution Time: 4.298 ms (5 rows) [exit=0] done