ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL 任意字段数组合 AND\OR 条件,指定返回结果条数,构造测试数据算法举例

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

原创 PostgreSQL 作者:digoal 时间:2018-10-09 14:41:37 0 删除 编辑

背景

在进行一些实际的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增加了多列统计信息)

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

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

单个字段的取值空间 =   X_/(A/Y)   (开X根)

例子

1、表记录数1000万

2、表字段数64

字段取值空间如何计算?

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

单个字段取值个数 =   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)。

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

单个字段的取值空间 =   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)。

原文地址:https://github.com/digoal/blog/blob/master/201809/20180905_03.md

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23503672/viewspace-2215704/,如需转载,请注明出处,否则将追究法律责任。

全部评论
周正中 阿里云高级技术专家, 网名: 德哥 ( digoal );PostgreSQL 中国社区 发起人之一;PostgreSQL 象牙塔 发起人之一;DBA+社群 联合发起人之一;10余项数据库相关专利;完成了对数据库HA、容灾、备份、恢复、分布式、数据仓库架构设计和建设;数据库管理和开发的标准化体系建立。

注册时间:2018-09-30

  • 博文量
    55
  • 访问量
    61177