I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred records that have a non-NULL value in both fields.
When I want to query all records that have both values set, the PostgreSQL query planner assumes that both columns are statistically independent, so it expects that the query will return 2.5M rows (50% of 50% of 10M total rows), while it actually returns a mere 1000 rows.
This overestimation of rows causes bad optimization decisions later on in the query planning process, because if I want to join the table to another table with many rows, it chooses a hash join, while a nested loop join would be multiple orders of magnitude quicker.
Below is a simplified definition of the tables that I am talking about:
CREATE TABLE a (
id serial PRIMARY KEY,
num1 integer,
num2 integer
);
-- partial index for finding rows with both values set
CREATE INDEX a_num1_num2_idx ON a (num1, num2)
WHERE num1 IS NOT NULL AND num2 IS NOT NULL;
CREATE TABLE b (
id serial PRIMARY KEY,
a_id integer REFERENCES a (id)
);
-- foreign key index
CREATE INDEX b_a_id_idx ON b (a_id);
-- 5M records with NULL in first column
INSERT INTO a (num1, num2)
SELECT NULL, random() * 1000000
FROM generate_series(1, 5000000);
-- 5M records with NULL in second column
INSERT INTO a (num1, num2)
SELECT random() * 1000000, NULL
FROM generate_series(1, 5000000);
-- 1000 records with both values set
UPDATE a SET
num1 = random() * 1000000,
num2 = random() * 1000000
WHERE random() < 0.0001;
-- add some records in other table to join to
INSERT INTO b (a_id)
SELECT id
FROM a;
The rather simple query below takes 8000ms with hash and merge joins enabled, and 50ms with only nested loop joins left enabled.
SELECT count(*) FROM a JOIN b ON a.id = b.a_id WHERE num1 IS NOT NULL AND num2 IS NOT NULL;
How can I properly fix this behavior, i.e. without setting enable_hashjoin and enable_mergejoin to off? I already tried adding more statistics, but without success so far.
