Waiting for Postgres 17: Faster B-Tree Index Scans for IN(...) lists and ANY =

Ғылым және технология

Find the article on our blog here:
pganalyze.com/blog/5mins-post...
In E111 of "5mins of Postgres" we discuss faster B-tree index scans in Postgres 17 for queries that involve IN lists or other cases where multiple array values are being passed to Postgres (ScalarArrayOpExpr). We show how even simple cases now avoid repeated page access, and how turning filters into index conditions and processing like an Index Skip Scan can yield significant speedups for certain queries.
Learn more about pganalyze:
pganalyze.com
pganalyze.com/newsletter
/ pganalyze
Check out the pganalyze library for eBooks, webinars, and more:
pganalyze.com/resources
📑 What we have discussed in this episode of 5mins of Postgres:
Enhance nbtree ScalarArrayOp execution - Postgres commit by Peter Geoghegan
git.postgresql.org/gitweb/?p=...
Teach btree to handle ScalarArrayOpExpr quals natively - Postgres commit by Tom Lane (2011)
git.postgresql.org/gitweb/?p=...
Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan - Postgres mailing list discussion
www.postgresql.org/message-id...)
Postgres documentation: The cumulative statistics system - pg_stat_all_indexes
www.postgresql.org/docs/devel...
The _bt_first() function in the Postgres source
github.com/postgres/postgres/...
H. Leslie, R. Jain, D. Birdsall and H. Yaghmai "Efficient Search of Multidimensional B-Trees" Proceedings of the 21st VLDB Conference, 1995
vldb.org/conf/1995/P710.PDF
Loose indexscan vs Index Skip Scan - Postgres wiki
wiki.postgresql.org/wiki/Loos...
GitHub Gist by Benoit Tigeot with example reproducer and query
gist.github.com/benoittgt/ab7...

Пікірлер: 3

  • @jocketf3083
    @jocketf30833 ай бұрын

    Awesome!

  • @andatki
    @andatki2 ай бұрын

    Woo hoo. Nice job to everyone involved on this as well, including you Lukas for helping raise awareness of this and connect everything together. In Active Record Rails apps, I’ve seen problematic IN clauses with a large amount of values, showing up in PGSS. My feeling is this is going to help a lot when this becomes available.

  • @LukasFittl

    @LukasFittl

    2 ай бұрын

    Yeah, I'm excited about what this will bring for the typical Rails app - these overly long IN lists are very much a Rails pattern that should see a noticeable performance benefit here with 17.

Келесі