Re: [PATCH] minor optimization for ineq_histogram_selectivity()

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