Indexing (B-Tree Indexes) | Tutorials

B-Tree Sample

We are going to review how to set up simple B-Tree indexes, the most common type of index. We’ve loaded some open weather data with data events by type, damage, time and location. For just a very basic index, I’m going to find everything for winter storms. Once I add this index, this means that to get this data, the database doesn't have to scan all the weather events to get me additional data about severe weather events, it already knows where to look for those.

Starting query


EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM weather
WHERE event_type = 'Winter Storm';

Before indexing, what does explain analyze tell us about execution time?

QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on weather  (cost=0.00..103.75 rows=2 width=2210) (actual time=0.400..80.900 rows=11 loops=1)
   Filter: ((event_type)::text = 'Winter Storm'::text)
   Rows Removed by Filter: 1189
 Planning Time: 93.000 ms
 Execution Time: 83.400 ms
(5 rows)

Now create an index


CREATE INDEX idx_weather_type ON weather(event_type);

Now run your query with EXPLAIN again


EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM weather
WHERE event_type = 'Winter Storm';

QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on weather  (cost=4.32..23.99 rows=6 width=2210) (actual time=3.700..4.600 rows=11 loops=1)
   Recheck Cond: ((event_type)::text = 'Winter Storm'::text)
   Heap Blocks: exact=6
   ->  Bitmap Index Scan on idx_weather_type  (cost=0.00..4.32 rows=6 width=0) (actual time=3.500..3.500 rows=11 loops=1)
         Index Cond: ((event_type)::text = 'Winter Storm'::text)
 Planning Time: 31.600 ms
 Execution Time: 6.900 ms
(7 rows)

Look at that drop in query execution time, cool!

Multicolumn B-Tree Indexes

Indexes aren't always created for single columns only - Postgres also supports multicolumn indexes. These can be useful if you know that you'll be querying a lot on multiple columns at once.

Drop the first index we made


DROP INDEX idx_weather_type;

Starting query


EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM weather
WHERE event_type = 'Winter Storm'
  AND damage_crops > '0';

Before index explain analyze

QUERY PLAN
---------------------------------------------
 Seq Scan on weather  (cost=0.00..118.00 rows=2 width=2210) (actual time=0.300..61.300 rows=11 loops=1)
   Filter: (((damage_crops)::text > '0'::text) AND ((event_type)::text = 'Winter Storm'::text))
   Rows Removed by Filter: 1189
 Planning Time: 16.700 ms
 Execution Time: 63.400 ms
(5 rows)

Multi-column Index for severity and type


CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);

After Index Explain Analyze


EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM weather
WHERE event_type = 'Winter Storm'
  AND damage_crops > '0';

QUERY PLAN
-------------------------------------------------------------------
 Bitmap Heap Scan on weather  (cost=4.30..11.48 rows=2 width=2210) (actual time=4.500..5.000 rows=11 loops=1)
   Recheck Cond: (((event_type)::text = 'Winter Storm'::text) AND ((damage_crops)::text > '0'::text))
   Heap Blocks: exact=6
   ->  Bitmap Index Scan on idx_storm_crop  (cost=0.00..4.30 rows=2 width=0) (actual time=1.000..1.000 rows=11 loops=1)
         Index Cond: (((event_type)::text = 'Winter Storm'::text) AND ((damage_crops)::text > '0'::text))
 Planning Time: 24.900 ms
 Execution Time: 7.300 ms

Reduction in query time again, yay!

Column order still matters for multicolumn B-trees: historically a query that only filtered on damage_crops would not use (event_type, damage_crops) well. Postgres 18's skip scan improves that case for equality-style lookups on later keys, but leading with the columns you filter most often is still the safer design.

Covering indexes with INCLUDE

If you often select a few non-filter columns along with your lookup key, add them with INCLUDE so Postgres can satisfy the query from the index alone (index-only scan) without visiting the heap for those columns:


CREATE INDEX idx_storm_crop_covering
  ON weather (event_type, damage_crops)
  INCLUDE (event_id, begin_date_time);

Keep INCLUDE lists short — every extra column widens the index and slows writes. Use EXPLAIN (ANALYZE, BUFFERS) (and IO on Postgres 18+) when you compare plans.

If you get lost with all your creating indexes and need to see what you’ve got, this will show you all indexes on a particular table:


SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'weather';