PostgreSQL 9.2 is including the index-only scan feature, which will help reduce I/O by preventing unnecessary access to heap data if you only need data from the index.
Besides 9.2 is still in development, it is possible to download a version for testing at http://www.postgresql.org/download/snapshots/ . It’s important to note that it doesn’t add new behaviors, but improves the way that indexes are used.
Today we will handle Index Only Scan and Visibility Map over two blogs.
Index Only Scan?
Index Only Scan is a function, which is added on PostgreSQL9.2. You can improve index scan performance through Index Only Scan. It is a same concept with covered (or Covering) index on other DB products. It is a way to read data only using index scan without approaching table after a user builds up mainly used columns on an index.
Index entry (leaf node of btree) in PostgreSQL has a pointer related to the index keys and tuple. On a general index scan, an index key is used to find index entry and fetch uses the pointer about the tuple. It takes access cost (disk random IO) to fetch the tuple which points a tuple pointer. In contrast, Index-Only Scan finds an index entry corresponding to index key and It reads key value of index entry. Therefore, the performance would be improved much better because it can diminish an expensive disk random IO.
The condition to perform Index Only Scan
- First, a user should build index type available to use Index Only Scan ( At this point, I wrote down this blog, Index Only Scan can be used only at the part of Btree and Gist.)
- Of course, a Qualification and target column about the query of the corresponding table should be indexed on a single index
An optimizer in PostgreSQL refer to the Index Only Scan if data satisfies above conditions. Index Only Scan is performed when predicted cost is not more expensive than other performing methods.
Example of Index only scan
However, If data is performed by Index Only Scan plan, it is not true that user can read all tuple only using an index. Because there is no information for version in an index tuple, it is impossible to judge whether a tuple is readable in the current transaction or not. To solve this problem, an executor checks Visibility Map for page pointed by index tuple and judge whether a table page is visible or not.
Next time we are going to talk about the rest of this story – Visibility Map. Don’t miss next blog
BITNINE GLOBAL INC., THE COMPANY SPECIALIZING IN GRAPH DATABASE
비트나인, 그래프 데이터베이스 전문 기업