Query Optimization

PostgreSQL’s query planner decides how to execute every query — which indexes to use, in which order to join tables, and how to aggregate results. For most queries it makes good decisions automatically, but when a query is slow, the planner’s choices are the first thing to investigate. Reading execution plans and understanding what influences the planner’s decisions is one of the most practical skills for any developer working with Postgres at scale.

Setup

Every plan in this lesson is real output from PostgreSQL 17.11 in Docker (postgres:17). The plan shapes should match yours. The estimated rows= and cost= numbers can differ slightly, because ANALYZE reads a random sample of the table. The timings will differ, because they depend on your machine and on what is already cached. Start a server that mounts your current directory at /work, then create a database called lab:

# on Git Bash for Windows, run `export MSYS_NO_PATHCONV=1` first so /work isn't rewritten
docker run -d --name pr-sql-opt -v "$PWD":/work -w /work -e POSTGRES_PASSWORD=secret postgres:17
docker exec pr-sql-opt createdb -U postgres lab
docker exec -it pr-sql-opt bash

$ docker run -d --name pr-sql-opt -v "$PWD":/work -w /work -e POSTGRES_PASSWORD=secret postgres:17
b8df88b069fad2bc065e17d7a4cd878373d9f4570d1df2d3724e90d2c83753c6
$ docker exec pr-sql-opt createdb -U postgres lab
$ docker exec -it pr-sql-opt bash

Save each .sql file below in your current directory and run it with psql from the shell the last command opens in /work. Load this seed first: 2,000 customers and 200,000 orders, about 10 MB of table.

-- opt_seed.sql
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    total       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP NOT NULL
);

-- keep autovacuum from refreshing statistics behind your back during the exercises
ALTER TABLE orders SET (autovacuum_enabled = false);

INSERT INTO customers (id, name, email)
SELECT n, 'customer ' || n, 'user' || n || '@example.com'
FROM generate_series(1, 2000) AS n;

INSERT INTO orders (id, customer_id, total, created_at)
SELECT n,
       (n % 2000) + 1,
       (n % 500) + 0.99,
       TIMESTAMP '2024-01-01' + (n % 366) * INTERVAL '1 day' + (n % 1440) * INTERVAL '1 minute'
FROM generate_series(1, 200000) AS n;

ANALYZE customers;
ANALYZE orders;

$ psql -U postgres -d lab -f opt_seed.sql
CREATE TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 2000
INSERT 0 200000
ANALYZE
ANALYZE

Each customer has exactly 100 orders (n % 2000), spread evenly through the table. The examples build on each other, so run them in order. The Practice section uses the same seed in a fresh shop database.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the plan without running the query. EXPLAIN ANALYZE runs it and adds what really happened to every plan node.

-- explain.sql
-- plan only: the query does not run
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- plan plus a real run: actual time, actual rows
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

$ psql -U postgres -d lab -f explain.sql
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..3774.00 rows=100 width=22)
   Filter: (customer_id = 42)
(2 rows)

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..3774.00 rows=100 width=22) (actual time=0.024..17.913 rows=100 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 199900
 Planning Time: 0.070 ms
 Execution Time: 17.947 ms
(5 rows)

Read the first line of each plan left to right:

• cost=0.00..3774.00 is in the planner’s own cost units, not milliseconds. The first number is the cost before the first row comes out, and the second is the cost for all rows.
• rows=100 is the estimated row count, and width=22 is the estimated row size in bytes.
• actual time=0.024..17.913 is real milliseconds to the first row and to the last row.
• rows=100 inside the actual part is the real count. loops is how many times the node ran.

Here the estimate is right, 100 rows (a rerun of the seed may estimate 99). The problem is Rows Removed by Filter: 199900: there is no index on customer_id, so PostgreSQL reads the whole table to find 100 rows. A large gap between the estimated and actual rows is a red flag: it often means the planner’s statistics are stale. Practice task 4 makes that happen on purpose.

Adding BUFFERS shows how many 8 kB pages each node touched. A hit came from PostgreSQL’s shared cache, and a read came from the operating system or disk.

-- buffers.sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-12-01';

$ psql -U postgres -d lab -f buffers.sql
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=62.00..3879.58 rows=16567 width=23) (actual time=0.510..17.745 rows=16926 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=1291
   ->  Seq Scan on orders o  (cost=0.00..3774.00 rows=16567 width=14) (actual time=0.027..14.123 rows=16926 loops=1)
         Filter: (created_at >= '2024-12-01 00:00:00'::timestamp without time zone)
         Rows Removed by Filter: 183074
         Buffers: shared hit=1274
   ->  Hash  (cost=37.00..37.00 rows=2000 width=17) (actual time=0.469..0.470 rows=2000 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 118kB
         Buffers: shared hit=17
         ->  Seq Scan on customers c  (cost=0.00..37.00 rows=2000 width=17) (actual time=0.003..0.176 rows=2000 loops=1)
               Buffers: shared hit=17
 Planning:
   Buffers: shared hit=220
 Planning Time: 0.526 ms
 Execution Time: 18.435 ms
(16 rows)

Read a plan from the most indented node outwards. Both tables are scanned, customers is loaded into a hash table (118 kB), and each matching order looks up its customer there. A parent’s Buffers include its children’s, so 1291 = 1274 + 17. The orders scan touched 1274 pages, which is the whole table: no index on created_at exists yet. Every page was a hit because the seed had just loaded the data. On a cold server you would see read= as well, and a higher time.

Scan Types

The scan node tells you how much of the table a query touches.

• Seq Scan reads every page. It is the right choice for small tables, or when a query returns a large fraction of the rows. On a large table returning a few rows, it usually means an index is missing, as in explain.sql above.
• Index Scan walks the index and fetches each matching row from the table (the heap) as it goes. It suits a handful of rows. Each fetch can be a random read, so it gets expensive for many rows.
• Bitmap Heap Scan first collects every matching row location from the index into a bitmap, then reads those heap pages in physical order, each page once. It is the middle ground for moderate result sets.
• Index Only Scan answers from the index alone. It needs an index that contains every column the query uses, and it skips the heap only for pages marked all-visible in the visibility map. Other pages still cost a heap fetch each. With every page all-visible, it is the best case.

-- scans.sql
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- one row by primary key
EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 42;

-- 100 rows for one customer, spread over the table
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

$ psql -U postgres -d lab -f scans.sql
CREATE INDEX
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=22) (actual time=0.006..0.007 rows=1 loops=1)
   Index Cond: (id = 42)
 Planning Time: 0.209 ms
 Execution Time: 0.036 ms
(4 rows)

                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5.07..314.02 rows=100 width=22) (actual time=0.041..0.137 rows=100 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=100
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.04 rows=100 width=0) (actual time=0.028..0.028 rows=100 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.032 ms
 Execution Time: 0.147 ms
(7 rows)

One row by primary key gets a plain Index Scan. The 100 orders for customer 42 sit on 100 different pages (Heap Blocks: exact=100), so the planner picks a Bitmap Heap Scan and reads each page once, in order. The same query that took about 18 ms as a Seq Scan now takes about 0.15 ms. The condition shows as Index Cond, not Filter, which is how you can tell an index did the narrowing.

Now try a covering index. The query below needs only customer_id and total, and both are in the new index, so you might expect an Index Only Scan straight away:

-- covering.sql
-- the query needs customer_id and total, and both are in this index
CREATE INDEX idx_orders_customer_total ON orders (customer_id, total);

EXPLAIN ANALYZE SELECT customer_id, total FROM orders WHERE customer_id = 42;

VACUUM orders;

EXPLAIN ANALYZE SELECT customer_id, total FROM orders WHERE customer_id = 42;

$ psql -U postgres -d lab -f covering.sql
CREATE INDEX
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5.07..314.02 rows=100 width=10) (actual time=0.023..0.123 rows=100 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=100
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.04 rows=100 width=0) (actual time=0.008..0.008 rows=100 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.240 ms
 Execution Time: 0.150 ms
(7 rows)

VACUUM
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_orders_customer_total on orders  (cost=0.42..6.17 rows=100 width=10) (actual time=0.037..0.051 rows=100 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.078 ms
 Execution Time: 0.064 ms
(5 rows)

The covering index alone was not enough. The table was freshly loaded and had never been vacuumed, so no page was marked all-visible in the visibility map. An index-only scan would still have had to visit the heap to check each row’s visibility, so the planner kept the bitmap scan and ignored the new index. VACUUM sets the visibility map, and then the same query becomes an Index Only Scan with Heap Fetches: 0. On a busy table, autovacuum normally does this for you. Here the seed turned it off for orders.

Join Strategies

The planner picks a join strategy from the sizes of the two inputs, the indexes available, and whether sorted output is useful.

• Nested Loop runs the inner side once for each outer row. It is cheap when the outer side is small and the inner side is an index lookup, and very expensive otherwise.
• Hash Join builds a hash table from the smaller input and probes it with every row of the larger one. It suits large, unsorted inputs, and costs memory for the hash table.
• Merge Join walks two inputs that are already sorted on the join key, side by side. It shines when both sides can be read in order from an index.

buffers.sql above already showed a Hash Join. This file adds a shipments table, one row for every second order, keyed by order_id, and shows the other two:

-- joins.sql
CREATE TABLE shipments AS
SELECT id AS order_id, created_at + INTERVAL '2 days' AS shipped_at
FROM orders WHERE id % 2 = 0;
ALTER TABLE shipments ADD PRIMARY KEY (order_id);
ANALYZE shipments;

-- three customers: a few hundred rows
EXPLAIN ANALYZE
SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 3;

-- every shipped order, sorted by order id
EXPLAIN ANALYZE
SELECT o.id, s.shipped_at FROM orders o JOIN shipments s ON s.order_id = o.id
ORDER BY o.id;

$ psql -U postgres -d lab -f joins.sql
SELECT 100000
ALTER TABLE
ANALYZE
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.70..29.84 rows=300 width=19) (actual time=0.040..0.369 rows=300 loops=1)
   ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.33 rows=3 width=17) (actual time=0.016..0.021 rows=3 loops=1)
         Index Cond: (id <= 3)
   ->  Index Only Scan using idx_orders_customer_total on orders o  (cost=0.42..6.17 rows=100 width=10) (actual time=0.015..0.093 rows=100 loops=3)
         Index Cond: (customer_id = c.id)
         Heap Fetches: 0
 Planning Time: 0.866 ms
 Execution Time: 0.439 ms
(8 rows)

                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.80..10138.09 rows=100000 width=12) (actual time=0.022..85.840 rows=100000 loops=1)
   Merge Cond: (o.id = s.order_id)
   ->  Index Only Scan using orders_pkey on orders o  (cost=0.42..5204.42 rows=200000 width=4) (actual time=0.013..29.960 rows=200000 loops=1)
         Heap Fetches: 0
   ->  Index Scan using shipments_pkey on shipments s  (cost=0.29..3183.74 rows=100000 width=12) (actual time=0.005..20.783 rows=100000 loops=1)
 Planning Time: 0.241 ms
 Execution Time: 90.520 ms
(7 rows)

In the Nested Loop, the inner Index Only Scan has loops=3: it ran once for each of the three customers and returned 100 rows each time. Its actual time and rows are per loop, so the node really produced 300 rows. The Merge Join reads both primary keys in order, so the rows come out already sorted for the ORDER BY and no Sort node is needed. Try the second query without the ORDER BY: once sorted output is no longer worth anything, the planner is free to pick a different strategy.

To test whether the planner chose well, switch a strategy off for your session and compare. The enable_* settings don’t forbid a strategy outright. They make it so expensive that the planner avoids it whenever it has another option.

-- force.sql
SET enable_nestloop = off;  -- diagnostic only, and only for this session
EXPLAIN ANALYZE
SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 3;
RESET enable_nestloop;

$ psql -U postgres -d lab -f force.sql
SET
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.37..3808.48 rows=300 width=19) (actual time=0.035..25.451 rows=300 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..3274.00 rows=200000 width=10) (actual time=0.007..11.528 rows=200000 loops=1)
   ->  Hash  (cost=8.33..8.33 rows=3 width=17) (actual time=0.015..0.016 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.33 rows=3 width=17) (actual time=0.007..0.008 rows=3 loops=1)
               Index Cond: (id <= 3)
 Planning Time: 0.734 ms
 Execution Time: 25.509 ms
(9 rows)

RESET

Without nested loops, the planner hashes the three customers and then has to scan all 200,000 orders to probe the hash. Its own estimate rises from 29.84 to 3808.48, and the real run takes about 25 ms instead of 0.44 ms, so the original choice was right. Use these settings only to diagnose, and never leave them set in production.

Keeping Statistics Fresh

The planner estimates how many rows each condition will match from statistics that ANALYZE collects into pg_statistic. You can read them through the pg_stats view. They go stale after heavy writes, and stale estimates lead to bad plans.

-- stats.sql
ANALYZE orders;

-- when was each table last analyzed, by you or by autovacuum?
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'customers');

-- part of what ANALYZE stored about two columns
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('customer_id', 'created_at');

$ psql -U postgres -d lab -f stats.sql
ANALYZE
  relname  |         last_analyze          | last_autoanalyze 
-----------+-------------------------------+------------------
 customers | 2026-10-11 16:16:05.271638+00 | 
 orders    | 2026-10-11 16:17:58.511232+00 | 
(2 rows)

   attname   | n_distinct | correlation  
-------------+------------+--------------
 customer_id |       2000 |  0.012539703
 created_at  |  -0.432215 | 0.0031662174
(2 rows)

customers still shows the time the seed analyzed it, and orders shows the run just now. last_autoanalyze is empty for both: the seed switched autovacuum off for orders, and it analyzed customers by hand straight after loading, so autovacuum had nothing to do. n_distinct = 2000 is the exact number of customers. A negative value is a fraction of the row count, so -0.43 means the planner expects about 43% of created_at values to be distinct. A correlation near 0 means the column’s order has nothing to do with the physical row order. That is why 100 orders for one customer sat on 100 different pages in scans.sql.

Autovacuum runs ANALYZE for you once enough rows change. After a bulk load, run it yourself before the first important query. Practice task 4 shows what a stale estimate looks like.

Common Anti-Patterns

These patterns quietly stop PostgreSQL from using an index. The examples use EXPLAIN (COSTS OFF), which prints just the plan shape. The thing to look for is whether the condition appears as an Index Cond or as a Filter on a Seq Scan.

A function on an indexed column defeats the index. The index stores created_at values, not DATE(created_at) values:

-- date_fn.sql
CREATE INDEX idx_orders_created ON orders (created_at);

-- bad: the function is applied to the column
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE DATE(created_at) = '2024-06-01';

-- good: the same day as a half-open range on the bare column
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

$ psql -U postgres -d lab -f date_fn.sql
CREATE INDEX
                             QUERY PLAN                              
---------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Seq Scan on orders
                     Filter: (date(created_at) = '2024-06-01'::date)
(6 rows)

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using idx_orders_created on orders
         Index Cond: ((created_at >= '2024-06-01 00:00:00'::timestamp without time zone) AND (created_at < '2024-06-02 00:00:00'::timestamp without time zone))
(3 rows)

With the function, every row is read (here by two processes in parallel) and date() is applied to each one. The range is on the bare column, so it becomes an Index Cond. Practice task 3 times the two versions.

A leading wildcard defeats a B-tree index, and so can the database’s collation. A B-tree can only match a string from its left end:

-- like.sql
CREATE INDEX idx_customers_email ON customers (email);
EXPLAIN (COSTS OFF) SELECT * FROM customers WHERE email LIKE 'user42%';

CREATE INDEX idx_customers_email_pattern ON customers (email text_pattern_ops);
EXPLAIN (COSTS OFF) SELECT * FROM customers WHERE email LIKE 'user42%';

-- a leading wildcard: no B-tree index can help
EXPLAIN (COSTS OFF) SELECT * FROM customers WHERE email LIKE '%42@example.com';

$ psql -U postgres -d lab -f like.sql
CREATE INDEX
              QUERY PLAN              
--------------------------------------
 Seq Scan on customers
   Filter: (email ~~ 'user42%'::text)
(2 rows)

CREATE INDEX
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Bitmap Heap Scan on customers
   Filter: (email ~~ 'user42%'::text)
   ->  Bitmap Index Scan on idx_customers_email_pattern
         Index Cond: ((email ~>=~ 'user42'::text) AND (email ~<~ 'user43'::text))
(4 rows)

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on customers
   Filter: (email ~~ '%42@example.com'::text)
(2 rows)

The surprise is the first plan. Even a prefix match like 'user42%' can’t use the plain index, because the database was created with the en_US.utf8 collation, the postgres image’s default (and the default on many installs). Under a linguistic collation, the index is not sorted character by character. A plain B-tree index only serves LIKE 'user42%' under the C collation. Under any other locale, build the index with text_pattern_ops, which compares byte by byte. The planner then turns the prefix into the range >= 'user42' AND < 'user43'. A leading % gives no prefix to build a range from, so it is a Seq Scan whatever the index. (For %...% searches, look at the pg_trgm extension and a GIN index.)

A type mismatch can defeat the index. It depends on whether the value has a type of its own:

-- cast.sql
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = '42';
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = 42.0;

$ psql -U postgres -d lab -f cast.sql
                   QUERY PLAN                   
------------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on idx_orders_customer
         Index Cond: (customer_id = 42)
(4 rows)

                   QUERY PLAN                   
------------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on idx_orders_customer
         Index Cond: (customer_id = 42)
(4 rows)

                   QUERY PLAN                    
-------------------------------------------------
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on orders
         Filter: ((customer_id)::numeric = 42.0)
(4 rows)

A quoted '42' is not a problem: an untyped literal takes the column’s type, so the plan is identical to plain 42. The trap is a value that already has a different type. 42.0 is numeric, so PostgreSQL casts the column instead, (customer_id)::numeric, and the integer index no longer matches. The same thing happens when a driver binds a numeric or float parameter against an integer column.

pg_stat_statements

This extension records statistics for every statement the server runs, grouped by query shape with constants replaced by $1. It is the quickest way to find the queries worth tuning first. It must be loaded when the server starts, so set it and restart:

-- pgss_enable.sql
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SHOW shared_preload_libraries;

$ psql -U postgres -d lab -f pgss_enable.sql
ALTER SYSTEM
 shared_preload_libraries 
--------------------------
 
(1 row)

$ docker restart pr-sql-opt
pr-sql-opt

ALTER SYSTEM writes the setting to postgresql.auto.conf, but SHOW is still empty, because this setting only takes effect at startup. Run docker restart from your host, not from inside the container, and open a new shell with docker exec -it pr-sql-opt bash afterwards. The restart ends the old one. Then create the extension, run a small workload, and ask for the top queries:

-- pgss.sql
SHOW shared_preload_libraries;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS reset;

-- a small workload; \o hides the results, \watch c=N runs a query N times
\o /dev/null
SELECT * FROM orders WHERE customer_id = 7 \watch i=0 c=200
SELECT count(*) FROM orders WHERE DATE(created_at) = '2024-06-01' \watch i=0 c=20
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.total > 400 \watch i=0 c=5
\o

-- top queries by total execution time: tune these first
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE query ILIKE '%orders%' AND query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;

$ psql -U postgres -d lab -f pgss.sql
 shared_preload_libraries 
--------------------------
 pg_stat_statements
(1 row)

CREATE EXTENSION
 reset 
-------
 t
(1 row)




 total_ms | calls | avg_ms |                            query                             
----------+-------+--------+--------------------------------------------------------------
   268.81 |    20 |  13.44 | SELECT count(*) FROM orders WHERE DATE(created_at) = $1
   219.85 |     5 |  43.97 | SELECT * FROM orders o JOIN customers c ON c.id = o.customer
    14.98 |   200 |   0.07 | SELECT * FROM orders WHERE customer_id = $1
(3 rows)

The blank lines are left over from the three \watch runs. The query called most often, 200 times, is at the bottom: each call takes 0.07 ms through the index. The top entry is the DATE(created_at) count from the anti-patterns section, at 13.44 ms a call. Sorting by total_exec_time ranks queries by how much server time they take overall, which makes it the most useful order. Swap the ORDER BY to mean_exec_time to find the slowest single calls instead.

Table Bloat and Autovacuum

PostgreSQL’s MVCC never overwrites a row in place. An UPDATE writes a new row version and leaves the old one dead. Dead rows take up space until VACUUM marks them reusable. Until then they make the table bigger and every Seq Scan slower.

-- bloat.sql
SELECT pg_size_pretty(pg_table_size('orders')) AS size_before;

-- every UPDATE writes a new row version and leaves the old one dead
UPDATE orders SET total = total + 1 WHERE id % 4 = 0;
SELECT pg_stat_force_next_flush();  -- publish this session's counters now

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

SELECT pg_size_pretty(pg_table_size('orders')) AS size_after_update;

$ psql -U postgres -d lab -f bloat.sql
 size_before 
-------------
 10224 kB
(1 row)

UPDATE 50000
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

 relname | n_live_tup | n_dead_tup | dead_pct |          last_vacuum          | last_autovacuum 
---------+------------+------------+----------+-------------------------------+-----------------
 orders  |     200000 |      50000 |     20.0 | 2026-10-11 16:16:07.759206+00 | 
(1 row)

 size_after_update 
-------------------
 12 MB
(1 row)

Updating a quarter of the rows left 50,000 dead versions, 20% of the table, and grew it from 10 MB to 12 MB. Activity counters are sent to the statistics system in batches, so pg_stat_force_next_flush() makes sure this session’s counts are visible straight away. last_vacuum is the VACUUM from covering.sql. Now reclaim the space:

-- vacuum.sql
VACUUM ANALYZE orders;
SELECT n_live_tup, n_dead_tup, last_vacuum
FROM pg_stat_user_tables WHERE relname = 'orders';
SELECT pg_size_pretty(pg_table_size('orders')) AS after_vacuum;

-- rewrites the table into new files; holds an ACCESS EXCLUSIVE lock meanwhile
VACUUM FULL orders;
SELECT pg_size_pretty(pg_table_size('orders')) AS after_vacuum_full;

$ psql -U postgres -d lab -f vacuum.sql
VACUUM
 n_live_tup | n_dead_tup |          last_vacuum          
------------+------------+-------------------------------
     200000 |          0 | 2026-10-11 16:19:03.828436+00
(1 row)

 after_vacuum 
--------------
 12 MB
(1 row)

VACUUM
 after_vacuum_full 
-------------------
 10192 kB
(1 row)

Plain VACUUM removed every dead row, but the table is still 12 MB. The freed space stays inside the table, ready for future inserts and updates, and isn’t returned to the operating system. That is normal and usually what you want. VACUUM FULL rewrites the table compactly and gets back to about 10 MB. It locks the table against reads and writes while it runs, so keep it for maintenance windows.

On a high-write table, autovacuum’s default trigger (dead rows past 20% of the table, which this UPDATE went beyond) can let a lot of bloat build up. Lower it for that table only:

-- autovac.sql
-- the seed switched autovacuum off for orders; switch it back on
ALTER TABLE orders RESET (autovacuum_enabled);

-- vacuum orders once 1% of its rows are dead, instead of the default 20%
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';
SHOW autovacuum_vacuum_scale_factor;

$ psql -U postgres -d lab -f autovac.sql
ALTER TABLE
ALTER TABLE
 relname |              reloptions               
---------+---------------------------------------
 orders  | {autovacuum_vacuum_scale_factor=0.01}
(1 row)

 autovacuum_vacuum_scale_factor 
--------------------------------
 0.2
(1 row)

The table-level option lives in reloptions and overrides the server-wide default, which SHOW still reports as 0.2 for every other table. The threshold is really autovacuum_vacuum_threshold (50 rows) plus the scale factor times the row count. For orders that is now 50 + 2,000 dead rows, instead of 40,050.

Quick Optimization Checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) and look for Seq Scans on large tables that return few rows.
2. Compare estimated and actual rows=. A large mismatch means stale statistics, so run ANALYZE.
3. Look for Nested Loops whose outer side is large and whose inner side has no index.
4. Index foreign key columns and columns you filter on often.
5. Use pg_stat_statements to find the queries with the highest total time.
6. Check WHERE clauses for functions on columns, leading wildcards and mismatched types.
7. Watch n_dead_tup and make sure autovacuum keeps up.

The Practice tasks below start from the same seed in a fresh database. Create it on this server with docker exec pr-sql-opt createdb -U postgres shop. When you’re done, docker rm -f pr-sql-opt removes the server.

Practice

The solutions were run on PostgreSQL 17.11 in Docker (postgres:17). Every timing below is real, but yours will differ. Create a database called shop and load this seed script first. It turns off autovacuum for orders, so statistics only change when you run ANALYZE or VACUUM yourself. Do the tasks in order, because each one keeps the indexes the previous one created.

-- opt_seed.sql
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    total       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP NOT NULL
);

-- keep autovacuum from refreshing statistics behind your back during the exercises
ALTER TABLE orders SET (autovacuum_enabled = false);

INSERT INTO customers (id, name, email)
SELECT n, 'customer ' || n, 'user' || n || '@example.com'
FROM generate_series(1, 2000) AS n;

INSERT INTO orders (id, customer_id, total, created_at)
SELECT n,
       (n % 2000) + 1,
       (n % 500) + 0.99,
       TIMESTAMP '2024-01-01' + (n % 366) * INTERVAL '1 day' + (n % 1440) * INTERVAL '1 minute'
FROM generate_series(1, 200000) AS n;

ANALYZE customers;
ANALYZE orders;

$ psql -U postgres -d shop -f opt_seed.sql
CREATE TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 2000
INSERT 0 200000
ANALYZE
ANALYZE

1. Run EXPLAIN ANALYZE on the lesson’s first query, customer_id = 42, then add an index on orders (customer_id) and run it again. Compare the scan type, Rows Removed by Filter and the execution time.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

$ psql -U postgres -d shop -f opt1.sql
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..3774.00 rows=99 width=22) (actual time=0.006..10.293 rows=100 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 199900
 Planning Time: 0.167 ms
 Execution Time: 10.320 ms
(5 rows)

CREATE INDEX
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5.06..311.24 rows=99 width=22) (actual time=0.025..0.119 rows=100 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=100
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.04 rows=99 width=0) (actual time=0.013..0.014 rows=100 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.162 ms
 Execution Time: 0.135 ms
(7 rows)

2. Create the lesson’s covering index on (customer_id, total) and run UPDATE orders SET total = total + 1 WHERE customer_id = 42;. Does SELECT customer_id, total ... WHERE customer_id = 42 get an Index Only Scan? Run VACUUM orders; and check again.

CREATE INDEX idx_orders_customer_total ON orders (customer_id, total);

UPDATE orders SET total = total + 1 WHERE customer_id = 42;

EXPLAIN ANALYZE SELECT customer_id, total FROM orders WHERE customer_id = 42;

VACUUM orders;

EXPLAIN ANALYZE SELECT customer_id, total FROM orders WHERE customer_id = 42;

$ psql -U postgres -d shop -f opt2.sql
CREATE INDEX
UPDATE 100
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5.07..314.06 rows=100 width=10) (actual time=0.345..0.355 rows=100 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=102
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.04 rows=100 width=0) (actual time=0.008..0.009 rows=200 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.044 ms
 Execution Time: 0.368 ms
(7 rows)

VACUUM
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_orders_customer_total on orders  (cost=0.42..6.15 rows=99 width=10) (actual time=0.024..0.029 rows=100 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.097 ms
 Execution Time: 0.041 ms
(5 rows)

3. Test the first anti-pattern. Index created_at, then count the orders for 1 June 2024, once with DATE(created_at) = '2024-06-01' and once with the half-open range from the lesson.

CREATE INDEX idx_orders_created ON orders (created_at);

EXPLAIN ANALYZE SELECT count(*) FROM orders WHERE DATE(created_at) = '2024-06-01';

EXPLAIN ANALYZE SELECT count(*) FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

$ psql -U postgres -d shop -f opt3.sql
CREATE INDEX
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=4041.29..4041.30 rows=1 width=8) (actual time=7.519..8.854 rows=1 loops=1)
   ->  Gather  (cost=4041.18..4041.29 rows=1 width=8) (actual time=7.436..8.849 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=3041.18..3041.19 rows=1 width=8) (actual time=6.010..6.011 rows=1 loops=2)
               ->  Parallel Seq Scan on orders  (cost=0.00..3039.71 rows=588 width=0) (actual time=0.011..5.994 rows=274 loops=2)
                     Filter: (date(created_at) = '2024-06-01'::date)
                     Rows Removed by Filter: 99726
 Planning Time: 0.244 ms
 Execution Time: 8.885 ms
(10 rows)

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21.45..21.46 rows=1 width=8) (actual time=0.085..0.086 rows=1 loops=1)
   ->  Index Only Scan using idx_orders_created on orders  (cost=0.42..20.00 rows=579 width=0) (actual time=0.036..0.065 rows=547 loops=1)
         Index Cond: ((created_at >= '2024-06-01 00:00:00'::timestamp without time zone) AND (created_at < '2024-06-02 00:00:00'::timestamp without time zone))
         Heap Fetches: 0
 Planning Time: 0.069 ms
 Execution Time: 0.098 ms
(6 rows)

4. Make the statistics stale on purpose: insert 50000 extra orders for customer 7, run EXPLAIN ANALYZE ... WHERE customer_id = 7, then ANALYZE orders; and run it again. Compare the estimated rows= with the actual rows=.

INSERT INTO orders (id, customer_id, total, created_at)
SELECT 200000 + n, 7, 9.99, TIMESTAMP '2025-01-01'
FROM generate_series(1, 50000) AS n;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 7;

ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 7;

$ psql -U postgres -d shop -f opt4.sql
INSERT 0 50000
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5.26..388.00 rows=124 width=22) (actual time=0.866..4.374 rows=50100 loops=1)
   Recheck Cond: (customer_id = 7)
   Heap Blocks: exact=419
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.22 rows=124 width=0) (actual time=0.825..0.826 rows=50100 loops=1)
         Index Cond: (customer_id = 7)
 Planning Time: 0.072 ms
 Execution Time: 5.455 ms
(7 rows)

ANALYZE
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=566.83..2796.18 rows=50908 width=22) (actual time=1.772..4.716 rows=50100 loops=1)
   Recheck Cond: (customer_id = 7)
   Heap Blocks: exact=419
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..554.10 rows=50908 width=0) (actual time=1.729..1.730 rows=50100 loops=1)
         Index Cond: (customer_id = 7)
 Planning Time: 0.152 ms
 Execution Time: 5.812 ms
(7 rows)

5. Join every order to its customer (orders to customers, no WHERE) and run it with EXPLAIN ANALYZE, then again with SET enable_hashjoin = off. Which strategy does the planner fall back to, and how much slower is it?

EXPLAIN ANALYZE
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;

SET enable_hashjoin = off;
EXPLAIN ANALYZE
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
SET enable_hashjoin = on;

$ psql -U postgres -d shop -f opt5.sql
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=62.00..4812.64 rows=250000 width=17) (actual time=0.357..42.338 rows=250000 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..4093.00 rows=250000 width=8) (actual time=0.003..10.580 rows=250000 loops=1)
   ->  Hash  (cost=37.00..37.00 rows=2000 width=17) (actual time=0.324..0.325 rows=2000 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 118kB
         ->  Seq Scan on customers c  (cost=0.00..37.00 rows=2000 width=17) (actual time=0.006..0.130 rows=2000 loops=1)
 Planning Time: 0.520 ms
 Execution Time: 48.269 ms
(8 rows)

SET
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..10932.20 rows=250000 width=17) (actual time=0.032..73.084 rows=250000 loops=1)
   ->  Seq Scan on orders o  (cost=0.00..4093.00 rows=250000 width=8) (actual time=0.007..9.850 rows=250000 loops=1)
   ->  Memoize  (cost=0.29..0.31 rows=1 width=17) (actual time=0.000..0.000 rows=1 loops=250000)
         Cache Key: o.customer_id
         Cache Mode: logical
         Hits: 248000  Misses: 2000  Evictions: 0  Overflows: 0  Memory Usage: 234kB
         ->  Index Scan using customers_pkey on customers c  (cost=0.28..0.30 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2000)
               Index Cond: (id = o.customer_id)
 Planning Time: 0.294 ms
 Execution Time: 78.605 ms
(10 rows)

SET