PostgreSQL任意字段數(shù)組合AND\OR條件分析

這篇文章主要講解了“PostgreSQL任意字段數(shù)組合AND\OR條件分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“PostgreSQL任意字段數(shù)組合AND\OR條件分析”吧！

背景

在進(jìn)行一些實(shí)際的POC測(cè)試時(shí)，需要根據(jù)業(yè)務(wù)提出的需求構(gòu)造數(shù)據(jù)，比如按照任意字段數(shù)組合 AND\OR 條件，指定返回結(jié)果條數(shù)，構(gòu)造測(cè)試數(shù)據(jù)。

需求

表記錄數(shù)A

表字段數(shù)B

1、N個(gè)字段等值OR，命中M條記錄

（兩個(gè)條件無(wú)法同時(shí)滿足）

2、X個(gè)字段等值A(chǔ)ND，命中Y條記錄

字段取值空間如何計(jì)算？

構(gòu)造算法

1、N個(gè)字段等值OR，命中M條記錄

單個(gè)字段單個(gè)VALUE的記錄數(shù) = M/N

單個(gè)字段取值個(gè)數(shù) = A/(M/N)

2、X個(gè)字段等值A(chǔ)ND，命中Y條記錄

（僅適用于完全離散分布，優(yōu)化器里最難估算的也是多個(gè)字段AND的選擇性，所以PG 10增加了多列統(tǒng)計(jì)信息）

X個(gè)字段的總?cè)≈悼臻g = A/Y

單個(gè)字段的取值空間 = X_/(A/Y) (開X根)

例子

1、表記錄數(shù)1000萬(wàn)

2、表字段數(shù)64

字段取值空間如何計(jì)算？

1、16個(gè)字段等值OR，命中1000條記錄

單個(gè)字段取值個(gè)數(shù) = 10000000/(1000/16.0) = 160000

1、建表，64字段，根據(jù)要求填入每個(gè)字段的取值范圍

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,';  -- 單個(gè)字段取值空間  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;  
end;  
$$;

根據(jù)前面提供的需求，寫入1000萬(wàn)記錄

insert into test1 select generate_series(1,10000000);

根據(jù)要求生成查詢SQL，16個(gè)字段組合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個(gè)字段 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實(shí)際返回?cái)?shù)

 count   
-------  
   905  
(1 row)

與算法預(yù)期基本一致(1000)。

2、16個(gè)字段等值A(chǔ)ND，命中20條記錄

單個(gè)字段的取值空間 = 16_/(10000000/20) = 2.27

1、根據(jù)算法，得到取值空間，創(chuàng)建測(cè)試表

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,';  -- 單個(gè)字段取值空間  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;  
end;  
$$;

寫入1000萬(wàn)數(shù)據(jù)

insert into test2 select generate_series(1,10000000);

生成測(cè)試SQL，16個(gè)字段，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個(gè)字段 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實(shí)際返回?cái)?shù)

 count   
-------  
   154  
(1 row)

與算法預(yù)期基本一致(取值范圍作了取舍2.27，降到了2)。

感謝各位的閱讀，以上就是“PostgreSQL任意字段數(shù)組合AND\OR條件分析”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)PostgreSQL任意字段數(shù)組合AND\OR條件分析這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！