【RDS for PostgreSQL】Database Insights で負荷の高いクエリを見つけて index を張るまでの一連の流れを試してみた

$ psql -h <RDSのエンドポイント> -U postgres -d postgres

...

postgres=> SELECT version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.10 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 12.4.0, 64-bit
(1 row)

-- サンプル DB 作成
postgres=> CREATE DATABASE blog_sample_db;
CREATE DATABASE

-- DB 切り替え
postgres=> \c blog_sample_db
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
You are now connected to database "blog_sample_db" as user "postgres".

-- 注文テーブルを想定した 300 万行のテーブルを作成
-- ※ customer_id に index は作成しない（あとで index 無しの遅さを確認するため）
blog_sample_db=> CREATE TABLE orders AS
SELECT g AS order_id,                           -- 注文ID（連番）
       (random()*100000)::int AS customer_id,   -- 顧客ID（ランダム値）
       (random()*1000)::int AS amount           -- 金額（ランダム値）
FROM generate_series(1, 3000000) g;             -- 300 万行分を生成
SELECT 3000000

-- テーブルの統計情報（行数や値の分布）を更新
blog_sample_db=> ANALYZE orders;
ANALYZE

-- 作成した orders テーブルの最初と最後の3行を表示
blog_sample_db=> (SELECT * FROM orders ORDER BY order_id ASC  LIMIT 3)
UNION ALL
(SELECT * FROM orders ORDER BY order_id DESC LIMIT 3);
 order_id | customer_id | amount 
----------+-------------+--------
        1 |       11846 |    803
        2 |       26822 |    597
        3 |       34804 |     29
  3000000 |       74301 |    440
  2999999 |       95633 |    494
  2999998 |       14675 |    828
(6 rows)

-- 行数と顧客ID数の確認
blog_sample_db=> SELECT count(*) AS 行数,
                        count(DISTINCT customer_id) AS 顧客id数
                        FROM orders;
  行数   | 顧客id数 
---------+----------
 3000000 |   100001
(1 row)

blog_sample_db=> \d orders
                 Table "public.orders"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 order_id    | integer |           |          | 
 customer_id | integer |           |          | 
 amount      | integer |           |          | 

 pgbench（アプリ利用者の代わり）
   ├─ 利用者1 ─┐
   ├─ 利用者2 ─┤  顧客IDで検索 ×繰り返し
   ├─  ...    ─┤ ───────────────▶  RDS（orders: index なし）
   └─ 利用者8 ─┘                    毎回300万行を読む → 負荷上昇

$ which pgbench
/usr/bin/pgbench

$ pgbench --version
pgbench (PostgreSQL) 17.10

$ cat > ./search.sql <<'EOF'
\set cid random(1, 100000)
SELECT count(*), sum(amount) FROM orders WHERE customer_id = :cid;
EOF

$ pgbench -h <RDSのエンドポイント> -U postgres -n -f ./search.sql -c 8 -j 2 -T 300 blog_sample_db

$ pgbench -h <RDSのエンドポイント> -U postgres -n -f ./search.sql -c 8 -j 2 -T 300 blog_sample_db
Password: 
pgbench (17.10)
transaction type: ./search.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
duration: 300 s
number of transactions actually processed: 3108
number of failed transactions: 0 (0.000%)
latency average = 773.183 ms
initial connection time = 99.782 ms
tps = 10.346840 (without initial connection time)

SELECT count(*), sum(amount) FROM orders WHERE customer_id = ?

blog_sample_db=> EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=32881.29..32881.30 rows=1 width=16) (actual time=96.479..97.499 rows=1 loops=1)
   Buffers: shared hit=16256
   ->  Gather  (cost=32881.07..32881.28 rows=2 width=16) (actual time=96.008..97.491 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=16256
         ->  Partial Aggregate  (cost=31881.06..31881.07 rows=1 width=16) (actual time=90.148..90.148 rows=1 loops=3)
               Buffers: shared hit=16256
               ->  Parallel Seq Scan on orders  (cost=0.00..31881.00 rows=13 width=4) (actual time=4.769..90.138 rows=9 loops=3)
                     Filter: (customer_id = 12345)
                     Rows Removed by Filter: 999991
                     Buffers: shared hit=16256
 Planning Time: 0.069 ms
 Execution Time: 97.541 ms
(14 rows)

blog_sample_db=> CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX

blog_sample_db=> ANALYZE orders;
ANALYZE

blog_sample_db=> \d orders
                 Table "public.orders"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 order_id    | integer |           |          | 
 customer_id | integer |           |          | 
 amount      | integer |           |          | 
Indexes:
    "idx_orders_customer_id" btree (customer_id)

blog_sample_db=> EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=125.15..125.16 rows=1 width=16) (actual time=0.065..0.066 rows=1 loops=1)
   Buffers: shared hit=26 read=3
   I/O Timings: shared read=0.009
   ->  Bitmap Heap Scan on orders  (cost=4.67..125.00 rows=31 width=4) (actual time=0.027..0.057 rows=26 loops=1)
         Recheck Cond: (customer_id = 12345)
         Heap Blocks: exact=26
         Buffers: shared hit=26 read=3
         I/O Timings: shared read=0.009
         ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.66 rows=31 width=0) (actual time=0.019..0.019 rows=26 loops=1)
               Index Cond: (customer_id = 12345)
               Buffers: shared read=3
               I/O Timings: shared read=0.009
 Planning:
   Buffers: shared hit=8 read=1
   I/O Timings: shared read=0.009
 Planning Time: 0.140 ms
 Execution Time: 0.102 ms
(17 rows)

$ pgbench -h <RDSのエンドポイント> -U postgres -n -f ./search.sql -c 8 -j 2 -T 300 blog_sample_db

transaction type: ./search.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
duration: 300 s
number of transactions actually processed: 3032481
number of failed transactions: 0 (0.000%)
latency average = 0.791 ms
initial connection time = 95.526 ms
tps = 10111.459660 (without initial connection time)