PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit === 1. 準備: 1 店舗が 99% を占める注文の表 === CREATE TABLE orders ( id bigint PRIMARY KEY, shop_id int NOT NULL, amount int NOT NULL ) WITH (autovacuum_enabled = off); CREATE TABLE INSERT INTO orders SELECT g, CASE WHEN g % 100 = 0 THEN 2 + (g / 100) % 1000 ELSE 1 END, g % 1000 FROM generate_series(1, 1000000) AS g; INSERT 0 1000000 CREATE INDEX orders_shop_id_idx ON orders (shop_id); CREATE INDEX VACUUM ANALYZE orders; VACUUM SELECT shop_id, count(*) FROM orders GROUP BY shop_id ORDER BY count(*) DESC LIMIT 3; shop_id | count ---------+-------- 1 | 990000 3 | 10 2 | 10 (3 rows) SELECT n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'shop_id'; n_distinct | most_common_vals | most_common_freqs ------------+------------------+------------------- 265 | {1} | {0.9901} (1 row) === 2. 同じ大口店舗 (shop_id = 1) で 7 回実行する === PREPARE q(int) AS SELECT sum(amount) FROM orders WHERE shop_id = $1; PREPARE --- 1 回目 EXPLAIN (ANALYZE) EXECUTE q(1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=12645.90..12645.91 rows=1 width=8) (actual time=44.494..48.911 rows=1 loops=1) -> Gather (cost=12645.69..12645.90 rows=2 width=8) (actual time=44.359..48.901 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=11645.69..11645.70 rows=1 width=8) (actual time=41.951..41.953 rows=1 loops=3) -> Parallel Seq Scan on orders (cost=0.00..10614.33 rows=412542 width=4) (actual time=0.010..25.834 rows=330000 loops=3) Filter: (shop_id = 1) Rows Removed by Filter: 3333 Planning Time: 0.070 ms Execution Time: 48.942 ms (10 rows) --- 2〜5 回目 EXECUTE q(1); sum ----------- 495000000 (1 row) EXECUTE q(1); sum ----------- 495000000 (1 row) EXECUTE q(1); sum ----------- 495000000 (1 row) EXECUTE q(1); sum ----------- 495000000 (1 row) --- 6 回目 EXPLAIN (ANALYZE) EXECUTE q(1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=505.44..505.45 rows=1 width=8) (actual time=165.601..165.603 rows=1 loops=1) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..496.00 rows=3774 width=4) (actual time=0.201..108.120 rows=990000 loops=1) Index Cond: (shop_id = $1) Planning Time: 0.636 ms Execution Time: 165.712 ms (5 rows) --- 7 回目 EXPLAIN (ANALYZE) EXECUTE q(1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=505.44..505.45 rows=1 width=8) (actual time=169.211..169.214 rows=1 loops=1) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..496.00 rows=3774 width=4) (actual time=0.017..109.127 rows=990000 loops=1) Index Cond: (shop_id = $1) Planning Time: 0.016 ms Execution Time: 169.259 ms (5 rows) SELECT name, generic_plans, custom_plans FROM pg_prepared_statements WHERE name = 'q'; name | generic_plans | custom_plans ------+---------------+-------------- q | 2 | 5 (1 row) === 3. 同じ generic plan で小さい店舗 (shop_id = 500) を引く === EXPLAIN (ANALYZE) EXECUTE q(500); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=505.44..505.45 rows=1 width=8) (actual time=0.068..0.068 rows=1 loops=1) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..496.00 rows=3774 width=4) (actual time=0.052..0.064 rows=10 loops=1) Index Cond: (shop_id = $1) Planning Time: 0.008 ms Execution Time: 0.087 ms (5 rows) === 4. force_custom_plan で値ごとの計画に戻す === SET plan_cache_mode = force_custom_plan; SET EXPLAIN (ANALYZE) EXECUTE q(1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=12645.90..12645.91 rows=1 width=8) (actual time=52.819..60.823 rows=1 loops=1) -> Gather (cost=12645.69..12645.90 rows=2 width=8) (actual time=52.686..60.811 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=11645.69..11645.70 rows=1 width=8) (actual time=49.700..49.702 rows=1 loops=3) -> Parallel Seq Scan on orders (cost=0.00..10614.33 rows=412542 width=4) (actual time=0.009..30.794 rows=330000 loops=3) Filter: (shop_id = 1) Rows Removed by Filter: 3333 Planning Time: 0.080 ms Execution Time: 60.861 ms (10 rows) RESET plan_cache_mode; RESET === 5. 小さい店舗で 6 回実行すると、generic plan に切り替わらない === PREPARE small(int) AS SELECT sum(amount) FROM orders WHERE shop_id = $1; PREPARE EXECUTE small(2); sum ----- 0 (1 row) EXECUTE small(3); sum ------ 1000 (1 row) EXECUTE small(4); sum ------ 2000 (1 row) EXECUTE small(5); sum ------ 3000 (1 row) EXECUTE small(6); sum ------ 4000 (1 row) EXPLAIN EXECUTE small(7); QUERY PLAN ----------------------------------------------------------------------------------------- Aggregate (cost=14.19..14.20 rows=1 width=8) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..14.10 rows=37 width=4) Index Cond: (shop_id = 7) (3 rows) SELECT name, generic_plans, custom_plans FROM pg_prepared_statements WHERE name = 'small'; name | generic_plans | custom_plans -------+---------------+-------------- small | 0 | 6 (1 row) === 6. 索引を消す・作ると、generic plan が作り直される === EXPLAIN (COSTS OFF) EXECUTE q(1); QUERY PLAN ----------------------------------------------------- Aggregate -> Index Scan using orders_shop_id_idx on orders Index Cond: (shop_id = $1) (3 rows) DROP INDEX orders_shop_id_idx; DROP INDEX EXPLAIN (COSTS OFF) EXECUTE q(1); QUERY PLAN ----------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on orders Filter: (shop_id = $1) (6 rows) CREATE INDEX orders_shop_id_idx ON orders (shop_id); CREATE INDEX EXPLAIN (COSTS OFF) EXECUTE q(1); QUERY PLAN ----------------------------------------------------- Aggregate -> Index Scan using orders_shop_id_idx on orders Index Cond: (shop_id = $1) (3 rows) === 7. 列を足すと、SELECT * の prepared statement はエラーになる === PREPARE one(bigint) AS SELECT * FROM orders WHERE id = $1; PREPARE EXECUTE one(100); id | shop_id | amount -----+---------+-------- 100 | 3 | 100 (1 row) ALTER TABLE orders ADD COLUMN note text; ALTER TABLE EXECUTE one(100); psql:/tmp/experiment.sql:67: ERROR: cached plan must not change result type DEALLOCATE one; DEALLOCATE PREPARE one(bigint) AS SELECT * FROM orders WHERE id = $1; PREPARE EXECUTE one(100); id | shop_id | amount | note -----+---------+--------+------ 100 | 3 | 100 | (1 row) ALTER TABLE orders DROP COLUMN note; ALTER TABLE === 8. search_path を変えると、表の名前が解決し直される === CREATE SCHEMA archive; CREATE SCHEMA CREATE TABLE archive.orders (id bigint, shop_id int, amount int); CREATE TABLE INSERT INTO archive.orders VALUES (1, 1, 100); INSERT 0 1 PREPARE cnt AS SELECT count(*) FROM orders; PREPARE EXECUTE cnt; count --------- 1000000 (1 row) SET search_path = archive; SET EXECUTE cnt; count ------- 1 (1 row) RESET search_path; RESET === 9. 統計を更新しても、作り直されない場合がある === ALTER TABLE orders ALTER COLUMN shop_id SET (n_distinct = 2); ALTER TABLE PREPARE st(int) AS SELECT sum(amount) FROM orders WHERE shop_id = $1; PREPARE EXECUTE st(1); sum ----------- 495000000 (1 row) EXECUTE st(1); sum ----------- 495000000 (1 row) EXECUTE st(1); sum ----------- 495000000 (1 row) EXECUTE st(1); sum ----------- 495000000 (1 row) EXECUTE st(1); sum ----------- 495000000 (1 row) --- ANALYZE 前の generic plan EXPLAIN EXECUTE st(1); QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=505.44..505.45 rows=1 width=8) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..496.00 rows=3774 width=4) Index Cond: (shop_id = $1) (3 rows) SELECT relpages, reltuples FROM pg_class WHERE oid = 'public.orders'::regclass; relpages | reltuples ----------+----------- 5406 | 1e+06 (1 row) ANALYZE orders; ANALYZE SELECT n_distinct FROM pg_stats WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'shop_id'; n_distinct ------------ 2 (1 row) SELECT relpages, reltuples FROM pg_class WHERE oid = 'public.orders'::regclass; relpages | reltuples ----------+----------- 5406 | 1e+06 (1 row) --- 行数もページ数も変わらない ANALYZE の後 EXPLAIN EXECUTE st(1); QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=505.44..505.45 rows=1 width=8) -> Index Scan using orders_shop_id_idx on orders (cost=0.42..496.00 rows=3774 width=4) Index Cond: (shop_id = $1) (3 rows) --- 新しく PREPARE した文の generic plan PREPARE st_new(int) AS SELECT sum(amount) FROM orders WHERE shop_id = $1; PREPARE SET plan_cache_mode = force_generic_plan; SET EXPLAIN EXECUTE st_new(1); QUERY PLAN ------------------------------------------------------------------------------------------ Finalize Aggregate (cost=12135.38..12135.39 rows=1 width=8) -> Gather (cost=12135.17..12135.38 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=11135.17..11135.18 rows=1 width=8) -> Parallel Seq Scan on orders (cost=0.00..10614.33 rows=208333 width=4) Filter: (shop_id = $1) (6 rows) RESET plan_cache_mode; RESET --- 1 件足してから ANALYZE した後 INSERT INTO orders VALUES (1000001, 1, 1); INSERT 0 1 ANALYZE orders; ANALYZE SELECT relpages, reltuples FROM pg_class WHERE oid = 'public.orders'::regclass; relpages | reltuples ----------+-------------- 5406 | 1.000001e+06 (1 row) EXPLAIN EXECUTE st(1); QUERY PLAN ------------------------------------------------------------------------------------------ Finalize Aggregate (cost=12135.39..12135.40 rows=1 width=8) -> Gather (cost=12135.17..12135.38 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=11135.17..11135.18 rows=1 width=8) -> Parallel Seq Scan on orders (cost=0.00..10614.34 rows=208333 width=4) Filter: (shop_id = $1) (6 rows)