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;
Without this patch, you will observe at least 4694 shared hits (which
are mostly heap fetches). If you apply the patch, you will observe very
few of them.
You should run the EXPLAIN on a standby, if you want to observe the heap
fetches more than one time (because of killed index tuples being ignored).
Best regards,
Frédéric
[1]
https://www.postgresql.org/message-id/flat/CAECtzeVPM4Oi6dTdqVQmjoLkDBVChNj7ed3hNs1RGrBbwCJ7Cw%40mail.gmail.com
