Hi hackers! Some time ago there was a discussion on detoast iterators [1]. The original thread stalled, but we took it, reviewed and refactored, and used it for some of our ideas.
I've refactored it onto the current master and made some tests,
iterative detoast provides significant improvement over fully detoasting
a value in cases like position() function. Please check out a POC patch
attached.
Below is simple test with fixed value (storage set to external to eliminate
compression influence):
postgres@postgres=# create table t (id int, t text);
CREATE TABLE
Time: 7.609 ms
postgres@postgres=# alter table t alter column t set storage external;
ALTER TABLE
Time: 8.496 ms
postgres@postgres=# insert into t (select i, i::text || 'abc' ||
repeat('a', 10000000) from generate_series(1, 1000) as i);
INSERT 0 1000
Time: 153096.834 ms (02:33.097)
master:
postgres@postgres=# select position('abc' in t) from t where id=599;
position
----------
4
(1 row)
Time: 47.346 ms
patched:
postgres@postgres=# select position('abc' in t) from t where id=599;
position
----------
4
(1 row)
Time: 7.607 ms
7.6 ms over 47.3 seems quite good.
[1]
https://www.postgresql.org/message-id/flat/CAL-OGks_onzpc9M9bXPCztMofWULcFkyeCeKiAgXzwRL8kXiag%40mail.gmail.com
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
v1-0001-detoast-iterator.patch
Description: Binary data
