PostgreSQL任意字段数组合AND\OR条件分析

71次阅读

共计 2437 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要讲解了“PostgreSQL 任意字段数组合 AND\OR 条件分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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

字段取值空间如何计算？

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

感谢各位的阅读，以上就是“PostgreSQL 任意字段数组合 AND\OR 条件分析”的内容了，经过本文的学习后，相信大家对 PostgreSQL 任意字段数组合 AND\OR 条件分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完