GSoC 2019 final report : de-TOAST'ing using an iterator

GSoC-2019-final-report.md

de-TOAST'ing using an iterator

GSoC 2019 final report

Organisation: PostgreSQL

Mentor: Andrey Borodin x4mmm@yandex-team.ru, Stephen Frost sfrost@snowman.net,
　　　　Tinsane vladimirlesk@yandex-team.ru, Atri Sharma atri.jiit@gmail.com
Student: Binguo Bao djydewang@gmail.com

Project Output

de-TOAST iterate API

The API provide the ability to de-TOAST a fully TOAST'd and compressed field using an iterator, satisfy the requirement that only need a slice of the entire value but don't know the size of the slice in advance, e.g. substring match or getting json object field.
mail list: https://www.postgresql.org/message-id/flat/CAL-OGks_onzpc9M9bXPCztMofWULcFkyeCeKiAgXzwRL8kXiag@mail.gmail.com

Optimize related quries

• Optimize substring() function
  mail list: https://www.postgresql.org/message-id/flat/CAL-OGkthU9Gs7TZchf5OWaL-Gsi=hXqufTxKv9qpNG73d5na_g@mail.gmail.com
• Optimize position() function
  mail list: https://www.postgresql.org/message-id/flat/CAL-OGks_onzpc9M9bXPCztMofWULcFkyeCeKiAgXzwRL8kXiag@mail.gmail.com
• Optimize json -> ->> #> and #>> operators
  the commit: https://github.com/djydewang/postgres/commit/568521e2f78a723b25532fb2c1b9cfe9c5447109

Impact

• improve performance of the substring() function, up to 14 times
• improve performance of the position() function, up to 35 times

Future work

• The current patchs are close to commit, but has not been merged into the postgres codebase
• The json-related optimization needs to wait for the de-TOAST iterator patch to be merged into the postgres codebase before submitting the patch.