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.comFrom d7602f0731a3c5cac59cf2fc81bc336f800cb11a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yh...@dalibo.com>
Date: Mon, 24 Oct 2022 16:33:26 +0200
Subject: [PATCH] minor optimization for ineq_histogram_selectivity()
Avoid calling of get_actual_variable_range() when possible.
With this change, 'probe' can still reach sslot.nvalues - 1 if needed,
so the algorithm still works correctly.
But if the right end of the hitogram bin is equal
to sslot.values[sslot.nvalues - 2], 'probe' will stay strictly less
than sslot.nvalues - 1 in the while loop, and we save a call to
get_actual_variable_range().
Use of get_actual_variable_range() can sometimes lead to surprising
slow planning time (see [1] and [2])
[1] https://www.postgresql.org/message-id/flat/CAECtzeVPM4Oi6dTdqVQmjoLkDBVChNj7ed3hNs1RGrBbwCJ7Cw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/36adf760-680b-7a4a-e019-64f4eaaf6ff7%40gmail.com
---
src/backend/utils/adt/selfuncs.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 69e0fb98f5..c1bc67705c 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -1108,7 +1108,7 @@ ineq_histogram_selectivity(PlannerInfo *root,
while (lobound < hibound)
{
- int probe = (lobound + hibound) / 2;
+ int probe = (lobound + hibound - 1) / 2;
bool ltcmp;
/*
--
2.30.2
