PostgreSQL 任意字段数组合 ANDOR 条件，指定返回结果条数，构造测试数据算法举例

需求

1、N个字段等值OR，命中M条记录

（两个条件无法同时满足）

2、X个字段等值AND，命中Y条记录

构造算法

1、N个字段等值OR，命中M条记录

2、X个字段等值AND，命中Y条记录

（仅适用于完全离散分布，优化器里最难估算的也是多个字段AND的选择性，所以PG 10增加了多列统计信息）

《PostgreSQL 10 黑科技 - 自定义统计信息》

X个字段的总取值空间 = `A/Y`

例子

1、表记录数1000万

2、表字段数64

1、16个字段等值OR，命中1000条记录

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;
\$\$;
``````

``````insert into test1 select generate_series(1,10000000);
``````

``````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;
\$\$;
``````

``````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)
``````

2、16个字段等值AND，命中20条记录

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;
\$\$;
``````

``````insert into test2 select generate_series(1,10000000);
``````

``````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;
\$\$;
``````

``````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)
``````

Tags:

Updated: