本文共 2447 字,大约阅读时间需要 8 分钟。
PostgreSQL , 构造测试数据 , 任意字段组合AND,OR查询 , 指定结果集大小
在进行一些实际的POC测试时,需要根据业务提出的需求构造数据,比如按照任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据。
表记录数A
表字段数B
1、N个字段等值OR,命中M条记录
(两个条件无法同时满足)
2、X个字段等值AND,命中Y条记录
字段取值空间如何计算?
1、N个字段等值OR,命中M条记录
单个字段单个VALUE的记录数 = M/N
单个字段取值个数 = A/(M/N)
2、X个字段等值AND,命中Y条记录
(仅适用于完全离散分布,优化器里最难估算的也是多个字段AND的选择性,所以PG 10增加了多列统计信息)
X个字段的总取值空间 = A/Y
单个字段的取值空间 = X_/(A/Y)
(开X根)
1、表记录数1000万
2、表字段数64
字段取值空间如何计算?
单个字段取值个数 = 10000000/(1000/16.0) = 160000
1、建表,64字段,根据要求填入每个字段的取值范围
do language plpgsql $$ declare sql text := 'create table test1 (id int, '; begin for i in 1..64 loop sql := sql||' c'||i||' int default random()*160000,'; -- 单个字段取值空间 end loop; sql := rtrim(sql,','); sql := sql||')'; execute sql; end; $$;
根据前面提供的需求,写入1000万记录
insert into test1 select generate_series(1,10000000);
根据要求生成查询SQL,16个字段组合OR
do language plpgsql $$ declare sql text := 'select count(*) from test1 where '; begin for i in 1..16 loop sql := sql||' c'||i||' ='||(random()*160000)::int||' or'; -- 16个字段 or 查询 end loop; sql := rtrim(sql,'or'); raise notice '%', sql; end; $$;
生成SQL
select count(*) from test1 where c1 =143477 or c2 =153395 or c3 =102052 or c4 =151143 or c5 =129060 or c6 =87519 or c7 =148787 or c8 =123117 or c9 =126622 or c10 =118215 or c11 =134245 or c12 =53791 or c13 =151020 or c14 =53076 or c15 =143204 or c16 =51640 ;
SQL实际返回数
count ------- 905 (1 row)
与算法预期基本一致(1000)。
单个字段的取值空间 = 16_/(10000000/20) = 2.27
1、根据算法,得到取值空间,创建测试表
do language plpgsql $$ declare sql text := 'create table test2 (id int, '; begin for i in 1..64 loop sql := sql||' c'||i||' int default random()*1,'; -- 单个字段取值空间 end loop; sql := rtrim(sql,','); sql := sql||')'; execute sql; end; $$;
写入1000万数据
insert into test2 select generate_series(1,10000000);
生成测试SQL,16个字段,OR查询
do language plpgsql $$ declare sql text := 'select count(*) from test2 where '; begin for i in 1..16 loop sql := sql||' c'||i||' ='||(random()*1)::int||' and'; -- 16个字段 and 查询 end loop; sql := rtrim(sql,'and'); raise notice '%', sql; end; $$;
生成SQL
select count(*) from test2 where c1 =1 and c2 =0 and c3 =0 and c4 =1 and c5 =1 and c6 =1 and c7 =0 and c8 =1 and c9 =0 and c10 =0 and c11 =0 and c12 =0 and c13 =0 and c14 =0 and c15 =1 and c16 =0;
SQL实际返回数
count ------- 154 (1 row)
与算法预期基本一致(取值范围作了取舍2.27,降到了2)。
转载地址:http://wuncx.baihongyu.com/