use_ruby_pg.md

• Create ruby_pg.rb that uses https://github.com/ged/ruby-pg . Ruby on Rails depends on it.

#!/usr/bin/env ruby

require 'pg'

# Output a table of current connections to the DB
conn = PG.connect( dbname: 'test' )
conn.exec('DROP TABLE IF EXISTS posts')
conn.exec('CREATE TABLE posts (id bigserial primary key)')
(1..10).each do |i|
  conn.exec('BEGIN');
  conn.exec('INSERT INTO posts DEFAULT VALUES RETURNING id')
  conn.exec('COMMIT')
  in_clause = "(#{(1..i).to_a.join(', ')})"
  p in_clause
  conn.exec("SELECT COUNT(*) FROM posts WHERE posts.id IN #{in_clause}")
end

• Confirm query_id_const_merge_threshold is set to 5 and run pg_stat_statements_reset()

postgres=# show pg_stat_statements.query_id_const_merge_threshold;
 pg_stat_statements.query_id_const_merge_threshold
---------------------------------------------------
 5
(1 row)

postgres=# select pg_stat_statements_reset();
   pg_stat_statements_reset
-------------------------------
 2024-03-26 08:47:24.424464+09
(1 row)

• Run ruby_pg.rb

$ ruby ruby_pg.rb
"(1)"
"(1, 2)"
"(1, 2, 3)"
"(1, 2, 3, 4)"
"(1, 2, 3, 4, 5)"
"(1, 2, 3, 4, 5, 6)"
"(1, 2, 3, 4, 5, 6, 7)"
"(1, 2, 3, 4, 5, 6, 7, 8)"
"(1, 2, 3, 4, 5, 6, 7, 8, 9)"
"(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)"

• Select pg_stat_statements and found the in clause values are not normalized

postgres=# select query,calls from pg_stat_statements where query like 'SELECT COUNT%' order by 1;
                                         query                                          | calls
----------------------------------------------------------------------------------------+-------
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1)                                      |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2)                                  |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3)                              |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4)                          |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5)                      |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5, $6)                  |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5, $6, $7)              |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5, $6, $7, $8)          |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)      |     1
 SELECT COUNT(*) FROM posts WHERE posts.id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) |     1
(10 rows)

postgres=#