PostgreSQL 10 High Performance

Full table scans

If you ask for data that does not have a selective index available, such as anything related to the unindexed v column, the query can only occur by running a full sequential scan over all the rows in the table:

EXPLAIN ANALYZE SELECT count(*) FROM t WHERE v=1;
QUERY PLAN
----------
Aggregate  (cost=1717.76..1717.77 rows=1 width=0) (actual time=74.535..74.537 rows=1 loops=1)
       ->  Seq Scan on t  (cost=0.00..1693.00 rows=9903 width=0) (actual time=0.015..56.847 rows=10054 loops=1)
             Filter: (v = 1)
     Total runtime: 74.600 ms
    
      
    
    seq_scan       | 1
    seq_tup_read   | 100000
    idx_scan       | 0
    idx_tup_fetch  | 0
    heap_blks_read | 0
    heap_blks_hit  | 443
    idx_blks_read  | 0
    idx_blks_hit   | 0
  

You can see that every single one of the 443 blocks in this table ...