CREATE TABLE a(id int); ALTER TABLE a ADD CONSTRAINT a_pkey PRIMARY KEY(id); INSERT INTO a(id) SELECT generate_series(1, 1000000); INSERT INTO a(id) SELECT generate_series(1000001, 10000000); CREATE TABLE b(id int, base_id int); ALTER TABLE b ADD CONSTRAINT b_pkey PRIMARY KEY(id); INSERT INTO b (id) select generate_series(1, 1000000); UPDATE b SET base_id = 1000000 - id; CREATE TABLE c(id int, base_id int); ALTER TABLE c ADD CONSTRAINT c_pkey PRIMARY KEY(id); INSERT INTO c (id) SELECT generate_series(1, 1000000); UPDATE c SET base_id = id / 10;
VACUUM ANALYZE; SET random_page_cost = 1.1; SET work_mem = '3276kB'; SET effective_cache_size = '90GB';
-- This gives an incorrect result of 999991, when 100000 is expected on Postgres 11.3 and 12 beta 1. SELECT COUNT (*) FROM a JOIN b ON a.id=b.base_id WHERE EXISTS ( SELECT 1 FROM c WHERE c.base_id = a.id );
I think it is correct result. This:
> UPDATE c SET base_id = id / 10;
would result in 9 rows (id from 1 to 9) to be updated with base_id = 0, as it should with integer division. These 9 rows will not match the condition: