*For the following query Index Only Scan to be performance as I am querying the indexed fields alone. There are 20 fields in this table, inclusive of the three I am selecting.* localdb=# explain analyse verbose select uid, guid from mm where uid=100 limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.27..8.29 rows=1 width=45) (actual time=0.017..0.017 rows=1 loops=1) Output: uid, guid -> Index Scan using mm_pkey on public.mm (cost=0.27..8.29 rows=1 width=45) (actual time=0.016..0.016 rows=1 loops=1) Output: uid, guid Index Cond: (mm.uid = 100) Planning time: 0.149 ms Execution time: 0.042 ms (7 rows)
*Index only scan runs for the following* localdb=# explain analyze verbose select id, z from test order by id limit 20; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.28..10.29 rows=20 width=17) (actual time=0.017..0.027 rows=20 loops=1) Output: id, z -> Index Only Scan using zx on public.test (cost=0.28..1502.47 rows=3000 width=17) (actual time=0.016..0.024 rows=20 loops=1) Output: id, z Heap Fetches: 20 Planning time: 0.080 ms Execution time: 0.050 ms (7 rows)
First guess would be that all the information needed from the index alone. In other words for an index only scan to work all fields retrieved have to be accessible from the same index.
