Access Patterns

Access Patterns

Transactional Versus Analytical Access Patterns

The clearest difference is how much data a query touches. EXPLAIN ANALYZE, which reports 8 kB buffers by default since PostgreSQL 18 1,289 , compares one order fetched by key with revenue per channel over all orders:

One order by key versus one aggregate over every orderSQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT status, total FROM orders WHERE order_id = 48213;
EXPLAIN (ANALYZE, COSTS OFF) SELECT channel, sum(total) FROM orders GROUP BY channel;

Output

 Index Scan using orders_pkey on orders (actual time=0.021..0.021 rows=1.00 loops=1)
   ...
   Buffers: shared hit=3
 ...
 Execution Time: 0.069 ms
 HashAggregate (actual time=68.842..68.845 rows=3.00 loops=1)
   ...
   ->  Seq Scan on orders (actual time=0.014..15.646 rows=100000.00 loops=1)
         Buffers: shared hit=1042
 ...
 Execution Time: 68.952 ms

The point query read three buffers (24 kB): two B-tree pages and one heap page. The aggregate read all 1,042 pages, 8.5 MB, 347 times more, to return three rows, using two of the nine columns it read. At a thousand times the data the first query gains perhaps one B-tree level; the second grows a thousandfold.

MENU