Postgres 9.6 Index-only scan

Question

Postgres has new release 9.6 - I have read the docs about the index-only scan. I'm aware of postgres tables' heap, and I know how partial indexes(or indexes in general) work. I just don't understand the benefit of index-only.

Questions

What really changed? Didn't we used index only scan already?
How and when should I use them to get the best benefits?

non related background: I'm dealing with tables with 6B rows with a lot of INSERTs and SELECTs in production environments

user330315 · Accepted Answer · 2016-09-29 17:06:43Z

6

Quote from the release notes

Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed

^{(emphasis mine)}

So in 9.5 and before the following index could not be used for an index only scan:

create index on foo (col1, col2)
   where col3 = 42;

Because col3 wasn't part of the indexed columns. This has changed in 9.6

answered Sep 29, 2016 at 17:06

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres 9.6 Index-only scan

Questions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Questions

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related