Analysis of AND\ OR conditions for the combination of arbitrary number of fields in PostgreSQL

This article mainly explains the "PostgreSQL arbitrary field number combination AND\ OR condition analysis", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "PostgreSQL arbitrary field number combination AND\ OR condition analysis"!

Background

When conducting some actual POC tests, you need to construct data according to the requirements put forward by the business, such as combining AND\ OR conditions according to any number of fields, specifying the number of results returned, and constructing test data.

Demand

Table record number A

Table number of fields B

1. N fields are equivalent to OR, hitting M records

(two conditions cannot be met at the same time)

2. X fields are equivalent to AND, hitting Y records

How to calculate the value space of the field?

Construction algorithm

1. N fields are equivalent to OR, hitting M records

The number of records in a single field and a single VALUE = M _ Pot N

The number of values for a single field = A / (MCMG N)

2. X fields are equivalent to AND, hitting Y records

(only for fully discrete distributions, the most difficult thing to estimate in the optimizer is the selectivity of multi-field AND, so PG 10 adds multi-column statistics.)

The total value space of X fields = A _ A _ Y

The value space of a single field = X values / (A _ Rank Y) (open X root)

Examples

1. The number of table records is 10 million

2. Number of table fields 64

How to calculate the value space of the field?

1. 16 fields are equivalent to OR, hitting 1000 records

The number of values for a single field = 100000000 / (1000Universe 16.0) = 160000

1. Create a table with 64 fields and fill in the value range of each field according to the requirements.

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 () * 1600000 sql,',');-- single field value space end loop; sql: = rtrim (sql,','); sql: = sql | |')'; execute sql; end; $$

Write 10 million records according to the requirements provided earlier

Insert into test1 select generate_series (1m 10000000)

Generate the query SQL,16 according to the requirements, combine the fields 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 fields or query end loop; sql: = rtrim (sql,'or'); raise notice'%', sql; end; $$

Generate 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 c9 = 126622 or c10 = 118215 or c11 = 134245 or c12 = 53791 or c13 = 151020 or c14 = 53076 or c15 = 143204 or c16 = 51640

Actual number of SQL returns

Count-905 (1 row)

It is basically consistent with the expectation of the algorithm (1000).

2. 16 fields are equivalent to AND, hitting 20 records

The value space of a single field = 1600 / (10000000x20) = 2.27

1. According to the algorithm, get the value space and create the test table.

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 token;-- single field value space end loop; sql: = rtrim (sql,','); sql: = sql |')'; execute sql; end; $$

Write 10 million data

Insert into test2 select generate_series (1m 10000000)

Generate test SQL,16 fields, OR query

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 fields and query end loop; sql: = rtrim (sql,'and'); raise notice'%', sql; end; $$

Generate 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

Actual number of SQL returns

Count-154 (1 row)

It is basically consistent with the expectation of the algorithm (the value range is 2.27, which is reduced to 2).

Thank you for your reading, the above is the content of "PostgreSQL arbitrary field number combination AND\ OR conditional analysis". After the study of this article, I believe you have a deeper understanding of the problem of PostgreSQL arbitrary field number combination AND\ OR conditional analysis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!