Index-Only Scans | Postgres.FM 109 | #PostgreSQL #Postgres podcast

[ 🇬🇧_🇺🇸 Check out the subtitles – we now edit them, ChatGPT+manually! You can also try YouTube's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]

Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice.

Here are some links to things they mentioned:

~~~

~~~

Postgres FM is produced by:

~~~

[kirkwolak]

We used these phrases when using Oracle.

Predicate Complete Queries: These are queries where the WHERE clauses are covered by indexes.

Query Complete Queries: Where every part of the Query is covered by indexes. Allowing for an Index-Only scan to happen, and be very efficient.

When moving to PostgreSQL, we found that there were more variables, like making sure the Visibility Map and statics were good so the Optimizer would choose the index.

The problem in PG is that even if it uses the index. It often has to refer back to the table to see if the record is visible. (Again, the Visibility Map helps).

Regardless, the value of indexing is huge. But also knowing when it is a waste of time. My smallest client has a TINY database. Recently moved to PG from SQLite as they grew to multiple users in geographically distant offices. Not even his Customer table will use an index... Because it's like 200 records. This table is likely cached. The query runs in a fraction of the planning time.

Expecting an index to be used in those cases... Kinda crazy. But we all have these tiny lookup tables. No need to index those to death...