Thread

1. Detoast iterators -take 2

   Nikita Malakhov <hukutoc@gmail.com> — 2025-08-26T07:41:53Z

   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/
   

2. Re: Detoast iterators -take 2

   Aleksander Alekseev <aleksander@tigerdata.com> — 2025-08-26T13:10:54Z

   Hi Nikita,
   
   Thanks for working on this.
   
   > master:
   > [...]
   > Time: 47.346 ms
   >
   > patched:
   > [...]
   > Time: 7.607 ms
   
   Looks impressive. I believe there is still some room for improvement though:
   
   1. IMO the new API (create_detoast_iterator et al) should have
   corresponding unit tests.
   2. Suggestion: perhaps (entity)_(action) naming would be better, that
   is: detoast_iterator_create(), detoast_iterator_free() etc.
   3. If you could demonstrate that there is no performance degradation
   for other scenarios that would be great.
   
   -- 
   Best regards,
   Aleksander Alekseev