[PATCH] minor optimization for ineq_histogram_selectivity()

From: Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: [PATCH] minor optimization for ineq_histogram_selectivity()
Date: 2022-10-24 15:26:50
Message-ID: e3ee2989-48ca-0f05-f50e-56250a621410@dalibo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-minor-optimization-for-ineq_histogram_selectivity.patch text/x-patch 1.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Arne Roland 2022-10-24 15:27:51 Re: Add the ability to limit the amount of memory that can be allocated to backends.
Previous Message Simon Riggs 2022-10-24 15:01:51 Re: New docs chapter on Transaction Management and related changes