On 10/24/22 17:26, Frédéric Yhuel wrote:
Hello,
When studying the weird planner issue reported here [1], I came up with
the attached patch. It reduces the probability of calling
get_actual_variable_range().
The patch applies to the master branch.
How to test :
CREATE TABLE foo (a bigint, b TEXT) WITH (autovacuum_enabled = off);
INSERT INTO foo SELECT i%213, md5(i::text) from
generate_series(1,1000000) i;
VACUUM ANALYZE foo;
SELECT * FROM pg_stats WHERE tablename = 'foo' AND attname='a'\gx
CREATE INDEX ON foo(a);
DELETE FROM foo WHERE a = 212;
EXPLAIN (BUFFERS) SELECT count(a) FROM foo WHERE a > 208;
With the above example, the variables "lobound", "hibound", and "probe"
would vary like this :
without patch :
lobound hibound probe
---------------------------------------
0 101 50
51 101 76
77 101 89
90 101 95
96 101 98
99 101 100
99 100 99
99 99
with patch :
lobound hibound probe
---------------------------------------
0 101 50
51 101 75
76 101 88
89 101 94
95 101 97
98 101 99
98 99 98
99 99
So we find the correct right end of the histogram bin (99) in both
cases, but "probe" doesn't reach 100 in the latter one, and
get_actual_variable_range() is never called.
Now, if we'd run the query SELECT count(a) FROM foo WHERE a > 211 :
without patch :
lobound hibound probe
---------------------------------------
0 101 50
51 101 76
77 101 89
90 101 95
96 101 98
99 101 100
99 100 99
100 100
with patch :
lobound hibound probe
---------------------------------------
0 101 50
51 101 75
76 101 88
89 101 94
95 101 97
98 101 99
100 101 100
100 100
Here, the correct right end of the histogram bin (100) is also found is
both cases.
I'm well aware that an example doesn't prove the correctness of an
algorithm, though.
Best regards,
Frédéric
