[HN Gopher] Introduction to PostgreSQL Indexes
       ___________________________________________________________________
        
       Introduction to PostgreSQL Indexes
        
       Author : dlt
       Score  : 275 points
       Date   : 2026-01-25 08:07 UTC (14 hours ago)
        
 (HTM) web link (dlt.github.io)
 (TXT) w3m dump (dlt.github.io)
        
       | joaomsa wrote:
       | Essential reading. More in-depth than an introduction, but
       | without being overly impenetrable except to those dealing with
       | the internals.
        
       | turbocon wrote:
       | This looks really awesome for Postgres
       | 
       | For general B Tree index resources this has been my got to site
       | for years https://use-the-index-luke.com/
        
       | cdiamand wrote:
       | Linking to the postgresql docs since they are very well written
       | and surprisingly enjoyable to read.
       | 
       | https://www.postgresql.org/docs/current/indexes-intro.html
        
       | jihadjihad wrote:
       | The section on multi-column indexes mirrors how I was taught and
       | how I've generally handled such indexes in the past. But is it
       | still true for more recent PG versions? I had an index and query
       | similar to the third example, and IIRC PG was able to use an
       | index, though I believe it was a bitmap index scan.
       | 
       | I am also unsure of the specific perf tradeoffs between index
       | scan types in that case, but when I saw that happen in the
       | EXPLAIN plan it was enough for me to call into question what had
       | been hardcoded wisdom in my mind for quite some time.
       | 
       | Further essential reading is the classic _Use The Index, Luke_
       | [0] site, and the book is a great buy for the whole team.
       | 
       | 0: https://use-the-index-luke.com/
        
         | petergeoghegan wrote:
         | > The section on multi-column indexes mirrors how I was taught
         | and how I've generally handled such indexes in the past. But is
         | it still true for more recent PG versions?
         | 
         | No, it isn't. PostgreSQL 18 added support for index skip scan:
         | 
         | https://youtu.be/RTXeA5svapg?si=_6q3mj1sJL8oLEWC&t=1366
         | 
         | It's actually possible to use a multicolumn index with a query
         | that only has operators on its lower-order columns in earlier
         | versions. But that requires a full index scan, which is usually
         | very inefficient.
        
           | dlt wrote:
           | Hi Peter, author here. Thanks for weighing in with the extra
           | context on index skip scan, and huge thanks for adding this
           | to Postgres.
           | 
           | I'm going to revise the multi-column index section to be more
           | precise about when leftmost-prefix rules apply, and I'll
           | include a note on how skip scan changes the picture
        
         | glenjamin wrote:
         | A bitmap index scan allows the database to narrow down which
         | pages could include the data, but then still has to recheck the
         | condition on the contents of those pages - so will still not be
         | as performant as an proper index scan
        
           | isbvhodnvemrwvn wrote:
           | With postgres indexes not containing liveness data for tuples
           | you'll have to hit quite a lot of those pages anyway, unless
           | they are frozen.
        
       | zozbot234 wrote:
       | It would be nice to see out-of-the-box support in PostgreSQL for
       | what's known as incremental view maintenance. It's very much an
       | index in that it gets updated automatically when the underlying
       | data changes, but it supports that for arbitrary views - not just
       | special-cased like ordinary database indexes.
        
         | BenoitP wrote:
         | A hard problem, especially wrt to transactions on a moving
         | target.
         | 
         | From memory, handful of projects just dedicated to this
         | dimension of databases: Noria, Materialize, Apache Flink, GCP's
         | Continuous Queries, Apache Spark Streaming Tables, Delta
         | Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB,
         | StreamSQL; and dozens more probably. IIRC, since this is about
         | postgres, there is recently created extension trying to deal
         | with this: pg_ivm
        
         | lispisok wrote:
         | If you have timeseries data TimescaleDB has this with
         | continuous aggregates
        
       | brudgers wrote:
       | Related, _Use the Index Luke_
       | 
       | https://use-the-index-luke.com/
        
       | Anonyneko wrote:
       | Is there a use-the-index-luke for MongoDB...?
        
       | zmmmmm wrote:
       | I love this style of writing. Simple, humble and direct transfer
       | of knowledge.
        
       ___________________________________________________________________
       (page generated 2026-01-25 23:00 UTC)