pgbench:生成随机填充字符串

Question

我的IMDb数据库位于PostgreSQL 16中，我正在尝试为性能基准生成随机字符串。相关的表是title_basics，其中包含主键列tconst，VARCHAR(9)。tconst的值为“tt0000000”，零表示数字，例如“tt0000001”和“of 9999999”。

我想要编写一个可以使用pgbench运行的.sql文件，它生成附加在字符串'tt‘之后的随机填充数字，例如：

SELECT * FROM title_basics WHERE tconst = 'tt0000001';

使用简单的SQL，这将是微不足道的，但是由于pgbench只支持少量的标量函数，这似乎很困难。我还探讨了在pgbench脚本中使用SQL的问题，但这样的构造似乎不受支持：

\set random_number (SELECT random());

或者：

\set random_number :randint(0, 9999999)
\set padding_length 7 - length(:'random_number')
\set primary_key 'tt' || repeat('0', :'padding_length') || :'random_number'

我还考虑使用SQL生成数字，但这实际上阻止了PostgreSQL在tconst上使用索引。我不想创建额外的索引来满足查询，因为由于其他原因，这是有问题的。

SELECT * 
FROM title_basics 
WHERE tconst = 'tt' || LPAD((FLOOR(RANDOM()*(9916880 - 1 + 1)) + 1)::text, 7, '0');

编辑：

我找到了一个在CTE中生成随机值的函数解决方案。这并不妨碍PostgreSQL在tconst上使用索引。然而，我很高兴看到一种更面向pgbench的方法。

WITH a AS (
SELECT 'tt' || LPAD((FLOOR(RANDOM()*(9916880 - 1 + 1)) + 1)::text, 7, '0') AS tconst_random
)
SELECT * 
FROM title_basics, a 
WHERE tconst = a.tconst_random;

Answer 1

random()在WHERE条件下将对每一行执行，每次返回一个不同的值，从而防止使用索引扫描。

传统的解决方法是子查询:如果没有引用任何外部列，则只执行一次(InitPlan)，因此结果是常量的，并且可以使用索引。

将random()替换为(SELECT random())。