PostgreSQL 10 High Performance

Simple index lookups

If you use the primary key on the table, which was built using an index, it's possible to retrieve a row using that index quite quickly. This uses what's unsurprisingly called an Index Scan:

EXPLAIN ANALYZE SELECT count(*) FROM t WHERE k=1000;
QUERY PLAN                                                   
----------
Aggregate  (cost=8.28..8.29 rows=1 width=0) (actual time=0.034..0.036 rows=1 loops=1)
       ->  Index Scan using t_pkey on t  (cost=0.00..8.28 rows=1 width=0) (actual time=0.020..0.023 rows=1 loops=1)
             Index Cond: (k = 1000)
     Total runtime: 0.104 ms
    
      
    
    seq_scan       | 0
    seq_tup_read   | 0
    idx_scan       | 1
    idx_tup_fetch  | 1
    heap_blks_read | 0
    heap_blks_hit  | 1
    idx_blks_read  | 0
    idx_blks_hit   | 3
  

The second part here demonstrates what comes out of the pg_stat_reset/table_stats ...