When I run the following, on PostgreSQL 13.5: SELECT single_value INTO pg_temp.single_value_table FROM generate_series(1, 1) AS single_value;
SELECT series_value, ARRAY[1] AS single_value_array INTO pg_temp.series_with_arrays FROM generate_series(1, 100000) AS series_value; ANALYZE pg_temp.single_value_table; ANALYZE pg_temp.series_with_arrays; EXPLAIN SELECT * FROM pg_temp.single_value_table JOIN pg_temp.series_with_arrays ON single_value_table.single_value = ANY(series_with_arrays.single_value_array) I get the following output: Nested Loop (cost=0.00..3987.01 rows=4889 width=33) Join Filter: (single_value_table.single_value = ANY (series_with_arrays.single_value_array)) -> Seq Scan on single_value_table (cost=0.00..1.01 rows=1 width=4) -> Seq Scan on series_with_arrays (cost=0.00..1736.00 rows=100000 width=29) Why is the planner expecting the number of rows to be reduced by the join filter? Is there any way I can correct the planner here? I've tried looking into pg_stats to figure this out, but I can't see why this is happening. I'm seeing the same issue on multiple databases so if anyone has time to help me understand this it would be greatly appreciated. In case it helps, I've noticed that the ratio changes based on the number of values held in the single_value_table so if you bump the single_value_table to be formed from generate_series(1, 20) instead the planned rows is much closer to correctly estimating the number of rows returned. Thanks for any help, Isaiah
