How Postgres Scans Tables

B-tree Indexes for Equality and Range Queries

-- Create a simple B-tree index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..18450.00 rows=52 width=96) (actual time=12.451..89.234 rows=48 loops=1)
   Filter: (customer_id = 12345)
   Rows Removed by Filter: 999952
 Planning Time: 0.085 ms
 Execution Time: 89.301 ms

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..56.84 rows=52 width=96) (actual time=0.028..0.142 rows=48 loops=1)
   Index Cond: (customer_id = 12345)
 Planning Time: 0.091 ms
 Execution Time: 0.168 ms

Multi-Column Indexes

-- Multi-column index
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Uses the index (leftmost prefix: status)
SELECT * FROM orders WHERE status = 'pending';

-- Uses the index (both columns)
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2025-01-01';

-- Uses the index (leftmost prefix for filter, second column for range)
SELECT * FROM orders WHERE status = 'pending' AND created_at BETWEEN '2025-01-01' AND '2025-06-01';

-- Does NOT use the index (missing leftmost column)
SELECT * FROM orders WHERE created_at > '2025-01-01';

UNIQUE Indexes and Covering Indexes

-- This creates a unique B-tree index automatically
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Equivalent to:
CREATE UNIQUE INDEX users_email_unique ON users(email);

-- Covering index: include columns needed by SELECT
CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (status, total_amount);

EXPLAIN ANALYZE SELECT status, total_amount FROM orders WHERE customer_id = 12345;

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_orders_covering on orders  (cost=0.42..52.10 rows=48 width=18) (actual time=0.024..0.089 rows=48 loops=1)
   Index Cond: (customer_id = 12345)
   Heap Fetches: 0
 Planning Time: 0.078 ms
 Execution Time: 0.112 ms

The Write Overhead Tradeoff

GIN, GiST, and Specialized Index Types

GIN — Generalized Inverted Index

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Find products with specific attributes (uses GIN index)
EXPLAIN ANALYZE
SELECT name, price FROM products
WHERE metadata @> '{"color": "blue", "size": "large"}';

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=24.01..1205.42 rows=312 width=38) (actual time=0.198..1.245 rows=287 loops=1)
   Recheck Cond: (metadata @> '{"color": "blue", "size": "large"}'::jsonb)
   Heap Blocks: exact=215
   ->  Bitmap Index Scan on idx_products_metadata  (cost=0.00..23.93 rows=312 width=0) (actual time=0.152..0.152 rows=287 loops=1)
         Index Cond: (metadata @> '{"color": "blue", "size": "large"}'::jsonb)
 Planning Time: 0.112 ms
 Execution Time: 1.398 ms

-- Smaller, faster — but only supports @> containment
CREATE INDEX idx_products_metadata_pathops ON products USING gin(metadata jsonb_path_ops);

-- Index an array column
CREATE INDEX idx_posts_tags ON posts USING gin(tags);

-- Query: find posts tagged with both 'postgres' and 'performance'
SELECT title FROM posts WHERE tags @> ARRAY['postgres', 'performance'];

GiST — Generalized Search Tree

-- GiST for range type queries
CREATE INDEX idx_reservations_during ON reservations USING gist(during);

-- Find overlapping reservations
SELECT * FROM reservations
WHERE during && tsrange('2025-03-01', '2025-03-07', '[]');

-- PostGIS: find the 5 nearest coffee shops
SELECT name, ST_Distance(location, ST_MakePoint(-122.4194, 37.7749)) AS dist
FROM shops
ORDER BY location <-> ST_MakePoint(-122.4194, 37.7749)
LIMIT 5;

Hash Indexes

-- Hash index — rarely needed, B-tree usually better
CREATE INDEX idx_sessions_token ON sessions USING hash(session_token);

Choosing the Right Index Type

Using pg_stat_statements to Find Your Slowest Queries

Enabling pg_stat_statements

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

aside positive If you're running self-managed Postgres, adding to shared_preload_libraries requires a full server restart (pg_ctl restart or systemctl restart postgresql). A reload is not sufficient. Plan this during a maintenance window.

Finding Queries That Need Indexes

-- Top 10 queries by total execution time
SELECT
  left(query, 80) AS query_preview,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS avg_ms,
  rows,
  round((shared_blks_read::numeric / nullif(shared_blks_hit + shared_blks_read, 0)) * 100, 1) AS cache_miss_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Identifying Sequential Scans in Your Heaviest Queries

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- paste the slow query here

Queries With High Buffer Reads

-- Queries reading the most data (I/O heavy)
SELECT
  left(query, 80) AS query_preview,
  calls,
  shared_blks_read + shared_blks_hit AS total_buffers,
  round((shared_blks_read + shared_blks_hit)::numeric / nullif(calls, 0), 0) AS buffers_per_call,
  round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY (shared_blks_read + shared_blks_hit) DESC
LIMIT 10;

Resetting Statistics

-- Reset pg_stat_statements (requires superuser)
SELECT pg_stat_statements_reset();

Partial Indexes, Expression Indexes, and BRIN

Partial Indexes

-- Only index active orders (not the 90% that are completed)
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status IN ('pending', 'processing', 'shipped');

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2025-06-01';

                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_active on orders  (cost=0.29..42.15 rows=18 width=96) (actual time=0.021..0.098 rows=15 loops=1)
   Index Cond: (created_at > '2025-06-01 00:00:00'::timestamp)
   Filter: (status = 'pending')
 Planning Time: 0.095 ms
 Execution Time: 0.128 ms

-- Soft deletes: only index non-deleted rows
CREATE INDEX idx_users_active_email ON users(email) WHERE deleted_at IS NULL;

-- Only index rows that need processing
CREATE INDEX idx_jobs_unprocessed ON jobs(priority, created_at) WHERE processed_at IS NULL;

Expression Indexes

-- Expression index for case-insensitive email lookups
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- The expression in the query must match the index expression exactly
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- Index on a date extraction (for queries that group by month)
CREATE INDEX idx_orders_month ON orders(date_trunc('month', created_at));

-- Index on a JSONB field extracted as text
CREATE INDEX idx_products_brand ON products((metadata->>'brand'));

BRIN — Block Range INdex

-- BRIN index: tiny index for physically ordered data
CREATE INDEX idx_events_created_brin ON events USING brin(created_at);

-- Compare index sizes
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'events' AND indexname LIKE '%created%';

          indexname           | index_size
-----------------------------+------------
 idx_events_created_btree    | 2142 MB
 idx_events_created_brin     | 104 kB

When Postgres Won't Use Your Index

The Table Is Too Small

-- A table with 200 rows: seq scan is correct behavior
EXPLAIN SELECT * FROM status_codes WHERE code = 'ACTIVE';

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on status_codes  (cost=0.00..1.25 rows=1 width=24)
   Filter: (code = 'ACTIVE'::text)

Low Selectivity — Too Many Rows Match

-- If 40% of orders have status = 'completed', the planner skips the index
EXPLAIN SELECT * FROM orders WHERE status = 'completed';

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..18450.00 rows=412000 width=96)
   Filter: (status = 'completed'::text)

Stale Statistics

-- After bulk loading data, update statistics
ANALYZE orders;

-- Check when statistics were last updated
SELECT
  schemaname || '.' || relname AS table_name,
  last_analyze,
  last_autoanalyze,
  n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Type Mismatch

-- Column is integer, but comparing to text — implicit cast may prevent index use
SELECT * FROM orders WHERE customer_id = '12345';

-- Fix: use the correct type
SELECT * FROM orders WHERE customer_id = 12345;

Functions on Indexed Columns

-- This does NOT use an index on the email column
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- Solution: create an expression index (covered in the previous section)
CREATE INDEX idx_users_email_lower ON users(lower(email));

Testing with enable_seqscan (Development Only)

-- NEVER use this in production — testing only
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';
SET enable_seqscan = on;

Monitoring Index Usage

Finding Unused Indexes

-- Find indexes that have never (or rarely) been used since last stats reset
SELECT
  schemaname || '.' || relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
  AND indexrelname NOT LIKE '%_unique%'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 15;

     table_name     |        index_name         | index_size | times_used | tuples_read | tuples_fetched
--------------------+---------------------------+------------+------------+-------------+----------------
 public.events      | idx_events_source         | 892 MB     |          0 |           0 |              0
 public.orders      | idx_orders_old_status     | 214 MB     |          0 |           0 |              0
 public.audit_log   | idx_audit_deprecated_col  | 156 MB     |          0 |           0 |              0

Finding Missing Indexes

-- Tables that might benefit from additional indexes
SELECT
  schemaname || '.' || relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  round(seq_scan::numeric / nullif(seq_scan + idx_scan, 0) * 100, 1) AS seq_scan_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
  AND seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 10;

     table_name     | seq_scan | seq_tup_read | idx_scan | n_live_tup | total_size | seq_scan_pct
--------------------+----------+--------------+----------+------------+------------+--------------
 public.events      |     8412 |  42058412000 |     1204 |    5012000 | 2.8 GB     |         87.5
 public.sessions    |     3201 |   1921600000 |    89421 |     600500 | 412 MB     |          3.5

Index Health Overview

-- Overall index health: what's being used, what's wasted
SELECT
  schemaname || '.' || relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scans,
  CASE
    WHEN idx_scan = 0 THEN 'UNUSED'
    WHEN idx_scan < 10 THEN 'RARELY_USED'
    ELSE 'ACTIVE'
  END AS status
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- When were stats last reset?
SELECT stats_reset FROM pg_stat_bgwriter;

aside positive Snowflake Postgres Insights surfaces index health automatically — including unused indexes that waste write performance and missing indexes suggested by query patterns. Monitor through the Insights dashboard or query the pg_stat_user_indexes view directly.

Conclusion

Related Resources

---

---

---