Learning PostgreSQL 11 - Third Edition

Cross-column correlation

Cross-column correlation can cause an incorrect estimation of the number of rows, as PostgreSQL assumes that each column is independent of other columns. In reality, there are a lot of examples where this isn't true. For example, you could find patterns where the first and last names in certain cultures are correlated. Another example is the country and language preference of clients. To understand cross-column correlation, let's create a table called client, as follows:

CREATE TABLE client (    id serial primary key,    name text,    country text,    language text);

To test the cross-correlation statistics, the following sample data is generated as follows:

INSERT INTO client(name, country, language) SELECT generate_random_text(8), ...