Index Only Scans - SELECT ve WHERE İçinde Sadece Index'li Sütun Var

Giriş

Açıklaması şöyle

Index Only scans are very similar to index scans except that they scan only the indexes and do not touch the table data. This is possible only if the query contains the indexed column in both the SELECT and WHERE clauses. 

SELECT *

Açıklaması şöyle. Yani SELECT * varsa Index Only Scan olamaz

Using SELECT * means that the database optimizer cannot choose index-only scans. For example, let’s say you need the IDs of students who scored above 90, and you have an index on the grades column that includes the student ID as a non-key, this index is perfect for this query.

However, since you asked for all fields, the database needs to access the heap data page to get the remaining fields increasing random reads resulting in far more I/Os. In contrast, the database could have only scanned the grades index and returned the IDs if you hadn’t used SELECT *.

Etkisiz Kılmak

Açıklaması şöyle

# To disable the index on session level run below query.
set enable_indexscan TO OFF;

Kullanım

Örnek

Şöyle yaparız. Burada id alanı için indeks yaratılıyor

CREATE INDEX id_idx ON fake_data USING BTREE(id);

Sorgu içinde sadece id alanını çekeriz şöyle yaparız

EXPLAIN ANALYZE SELECT id FROM fake_data WHERE 
 fake_data.id = 1000;

Örnek

Şöyle yaparız. Burada select içinde indeks sütunu ismi geçmiyor ama COUNT(*) aynı anlama geliyor. WHERE koşulunda da sadece indekslenmiş alan var.

> CREATE TABLE grades(id SERIAL NOT NULL, g INT NOT NULL);
> CREATE INDEX grades_index ON grades(g);

> EXPLAIN ANALYZE SELECT COUNT(*) FROM grades  WHERE g=30;
   QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=231.09..231.09 rows=1 width=8) (actual time=4.709..4.711 rows=1 loops=1)
   ->  Index Only Scan using grades_index on grades  (cost=0.42..206.75 rows=9733 width=0) (actual time=0.052..3.322 rows=10038 loops=1)
         Index Cond: (g = 30)
         Heap Fetches: 0
 Planning Time: 0.207 ms
 Execution Time: 4.774 ms