쿼리 처리 과정

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]