Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Introduction to PostgreSQL Indexes

dlt.github.io

Post reply on HN
Print view View original

11–19 of 19 posts

Re: Introduction to PostgreSQL Indexes

#11

Post by dlt »

petergeoghegan wrote: post #9
jihadjihad wrote: post #5

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…

> 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 versio…

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

Re: Introduction to PostgreSQL Indexes

#15

Post by lispisok »

zozbot234 wrote: post #6

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.

If you have timeseries data TimescaleDB has this with continuous aggregates

Re: Introduction to PostgreSQL Indexes

#16

Post by isbvhodnvemrwvn »

glenjamin wrote: post #10
jihadjihad wrote: post #5

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…

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

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.

Re: Introduction to PostgreSQL Indexes

#18

Post by augusteo »

Good timing for this article. The multi-column index advice was always confusing because the "leading column" rules had real performance implications, but bitmap index scans made it less catastrophic than the textbooks suggested.

Skip scan in PG 18 changes a lot of that conventional wisdom. Worth updating the mental model for anyone who learned indexing on older versions.

Re: Introduction to PostgreSQL Indexes

#19

Post by morshu9001 »

The whole btree vs hash discussion is interesting. Many people assume "ID" columns should be hash, but iirc the default btree works best for those. Also treelike structures are fundamentally better for nearly-sequential value insertion.

The blog post that this links to comes to the opposite conclusion though, showing hash winning the benchmarks.

Post reply on HN