Indexes, and reading EXPLAIN

By the end you'll know

What an index is, and why it makes finding rows fast
How to read EXPLAIN and EXPLAIN ANALYZE: sequential scans, index scans, and the timings
Why the database sometimes ignores an index, and is right to
Three ways to write a query so an index can't help it
What indexes cost: disk space, and slower writes
Why OFFSET gets slow, and the faster way to page

Before you start

You need: Lessons 1 to 8 done, with the larkspur container running.
Where commands run: inside psql: docker exec -it larkspur psql -U postgres -d larkspur. Turn on \timing from Lesson 2: this lesson is about how long things take.
Your numbers will differ. Timings depend on your machine, and the made-up data is random. The patterns, which plan, how many times faster, will be the same.
Heads up: the table you'll build takes a few seconds to make and about 100 MB of disk.

A table worth indexing

Twelve towns is too small for speed to matter: any query on it is instant. To see what an index does, you need a big table. Make one: a million service requests, the kind a town's 311 line logs (potholes, streetlights, noise), spread over five years.

SQL

SELECT setseed(0.42);
CREATE TABLE requests (
    id        integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    town_id   integer NOT NULL REFERENCES towns (id),
    category  text    NOT NULL,
    opened_on date    NOT NULL,
    status    text    NOT NULL
);
INSERT INTO requests (town_id, category, opened_on, status)
SELECT 1 + floor(random() * 12)::int,
       (ARRAY['pothole', 'streetlight', 'trash', 'noise', 'graffiti', 'water'])[1 + floor(random() * 6)::int],
       date '2021-01-01' + floor(random() * 1826)::int,
       (ARRAY['open', 'closed', 'closed', 'closed'])[1 + floor(random() * 4)::int]
FROM generate_series(1, 1000000);

OUTPUT

INSERT 0 1000000
Time: 2544.414 ms (00:02.544)

generate_series(1, 1000000) makes a million rows, one per number, and the SELECT turns each into a request: a random town, a random category from the list, a random day in the five years from 2021, and three times as likely closed as open. setseed makes random() give the same sequence each time, so your data matches this lesson's. A million rows in two and a half seconds.

Then tell the database to look at what it's got:

SQL

ANALYZE requests;
SELECT pg_size_pretty(pg_relation_size('requests')) AS table_size, count(*) FROM requests;
SELECT * FROM requests ORDER BY id LIMIT 3;

OUTPUT

ANALYZE
 table_size |  count  
------------+---------
 57 MB      | 1000000
(1 row)

 id | town_id |  category   | opened_on  | status 
----+---------+-------------+------------+--------
  1 |       9 | trash       | 2025-09-07 | open
  2 |       2 | streetlight | 2023-06-16 | closed
  3 |       7 | graffiti    | 2023-11-06 | closed
(3 rows)

ANALYZE samples the table and stores statistics: how many rows, how the values are spread. The database uses them to decide how to run each query, so they matter more than they sound. Postgres runs it by itself in the background too, but not straight away after a big load.

Without an index: EXPLAIN

How many requests were opened on July 4th, 2024? Before running it, ask the database how it would. EXPLAIN shows the query plan: the steps it has chosen, without running them.

SQL

EXPLAIN SELECT * FROM requests WHERE opened_on = '2024-07-04';

OUTPUT

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather  (cost=1000.00..13615.83 rows=545 width=25)
   Workers Planned: 2
   ->  Parallel Seq Scan on requests  (cost=0.00..12561.33 rows=227 width=25)
         Filter: (opened_on = '2024-07-04'::date)
(4 rows)

Read a plan from the most indented line outwards: the inner steps run first, and feed the ones above them.

Seq Scan is a sequential scan: read every row in the table, and keep the ones that pass the Filter. Parallel means the work is split, here between the main process and 2 workers, each reading a third.
Gather collects the workers' rows together.
cost is the planner's estimate of the work, in its own units: the first number is the cost before the first row comes out, the second for all of them. Useful for comparing plans, meaningless on its own.
rows=545 is its estimate of how many rows will match, from the statistics.

EXPLAIN ANALYZE runs the query for real, and adds what actually happened:

SQL

EXPLAIN ANALYZE SELECT * FROM requests WHERE opened_on = '2024-07-04';

OUTPUT

                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..13615.83 rows=545 width=25) (actual time=0.161..11.959 rows=549 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on requests  (cost=0.00..12561.33 rows=227 width=25) (actual time=0.096..9.638 rows=183 loops=3)
         Filter: (opened_on = '2024-07-04'::date)
         Rows Removed by Filter: 333150
 Planning Time: 0.016 ms
 Execution Time: 11.977 ms
(8 rows)

actual time is in milliseconds: when the first row came out, and when the last did.
rows=549: the real count, close to the estimate of 545, so the statistics are good. When the estimate and the real number are far apart, the planner was guessing badly, and that's often why a query is slow.
loops=3: the scan ran three times, once per process, finding 183 rows each.
Rows Removed by Filter: 333150: each process read a third of the table, a third of a million rows, to keep 183. To find 549 rows, it read all million.

About 12 milliseconds. Fast, for a million rows. But it's reading the whole table to answer a question about one day, and that cost grows with the table: ten million rows, ten times as long.

With an index

An index is a separate, sorted copy of one or more columns, each entry pointing to its row. Think of the index at the back of a book. To find every page that mentions "drainage", you don't read the book; you look up "d", find "drainage", and go straight to pages 41, 87 and 203. The index costs a few pages, and has to be redone in every new edition, but nobody would read a reference book without one.

Make one on opened_on:

SQL

CREATE INDEX requests_opened_on ON requests (opened_on);

OUTPUT

CREATE INDEX
Time: 161.313 ms

The same question:

SQL

EXPLAIN ANALYZE SELECT * FROM requests WHERE opened_on = '2024-07-04';

OUTPUT

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on requests  (cost=8.65..1697.41 rows=545 width=25) (actual time=0.082..0.463 rows=549 loops=1)
   Recheck Cond: (opened_on = '2024-07-04'::date)
   Heap Blocks: exact=529
   ->  Bitmap Index Scan on requests_opened_on  (cost=0.00..8.51 rows=545 width=0) (actual time=0.049..0.049 rows=549 loops=1)
         Index Cond: (opened_on = '2024-07-04'::date)
 Planning Time: 0.082 ms
 Execution Time: 0.481 ms
(7 rows)

0.48 milliseconds, down from 12: 25 times faster. And the gap grows with the table: the index lookup barely changes with ten times the rows; the full scan takes ten times as long. Reading inside out:

Bitmap Index Scan on requests_opened_on: look up July 4th in the index, and note where each of the 549 matching rows lives. That took 0.049 ms.
Bitmap Heap Scan: fetch those rows from the table (the heap, Postgres's name for the table's own storage), visiting 529 blocks, the 8 KB pages the table is stored in. Not the whole 57 MB.

A bitmap scan collects the locations first, then visits the pages in storage order, so a page holding several matches is read once. With only a few matches, you'll see a plain Index Scan instead, going row by row. Either way, it's the index doing the finding.

A range works too, because the index is sorted:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests
WHERE opened_on BETWEEN '2024-07-01' AND '2024-07-31';

OUTPUT

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Aggregate (actual time=4.925..4.925 rows=1 loops=1)
   ->  Bitmap Heap Scan on requests (actual time=1.038..4.600 rows=16715 loops=1)
         Recheck Cond: ((opened_on >= '2024-07-01'::date) AND (opened_on <= '2024-07-31'::date))
         Heap Blocks: exact=6639
         ->  Bitmap Index Scan on requests_opened_on (actual time=0.587..0.587 rows=16715 loops=1)
               Index Cond: ((opened_on >= '2024-07-01'::date) AND (opened_on <= '2024-07-31'::date))
 Planning Time: 0.021 ms
 Execution Time: 4.930 ms
(8 rows)

COSTS OFF leaves out the estimates, to keep the plans short from here on. A month of requests, 16,715 of them, in 5 ms. Aggregate is the count(*), done on the rows the scan hands up.

When the index is ignored on purpose

Every request since the start of 2022:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests WHERE opened_on >= '2022-01-01';

OUTPUT

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=21.626..22.795 rows=1 loops=1)
   ->  Gather (actual time=21.506..22.792 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=20.544..20.544 rows=1 loops=3)
               ->  Parallel Seq Scan on requests (actual time=0.005..14.551 rows=266597 loops=3)
                     Filter: (opened_on >= '2022-01-01'::date)
                     Rows Removed by Filter: 66736
 Planning Time: 0.019 ms
 Execution Time: 22.804 ms
(10 rows)

A sequential scan, with the index right there. That's the planner being right. Four years out of five is 80% of the table. Looking each one up in the index, then jumping to its page, is more work than just reading the table from start to finish. If you're going to read most of the book anyway, you don't use the index at the back.

So an index helps when a query picks out a small share of the rows. How small depends on the table, and the planner decides, using the statistics from ANALYZE. (Each worker counts its third, in a Partial Aggregate, and Finalize Aggregate adds the three counts together.)

Three ways to stop an index being used

1. Wrapping the column in a function. July 2024 again, written a different way:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests
WHERE extract(year FROM opened_on) = 2024 AND extract(month FROM opened_on) = 7;

OUTPUT

                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=30.075..31.517 rows=1 loops=1)
   ->  Gather (actual time=30.025..31.515 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=28.892..28.892 rows=1 loops=3)
               ->  Parallel Seq Scan on requests (actual time=0.016..28.737 rows=5572 loops=3)
                     Filter: ((EXTRACT(year FROM opened_on) = '2024'::numeric) AND (EXTRACT(month FROM opened_on) = '7'::numeric))
                     Rows Removed by Filter: 327762
 Planning Time: 0.057 ms
 Execution Time: 31.526 ms
(10 rows)

The same 16,715 requests (the plan's rows=5572 is an average per process, across three), but a full scan, and 31 ms. The index is sorted by opened_on, not by "the year of opened_on". To use it, the database would have to work out the year of every row first, which means reading every row. Leave the column bare, and put the work on the other side:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests
WHERE opened_on >= '2024-07-01' AND opened_on < '2024-08-01';

OUTPUT

                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Aggregate (actual time=4.768..4.769 rows=1 loops=1)
   ->  Bitmap Heap Scan on requests (actual time=0.917..4.438 rows=16715 loops=1)
         Recheck Cond: ((opened_on >= '2024-07-01'::date) AND (opened_on < '2024-08-01'::date))
         Heap Blocks: exact=6639
         ->  Bitmap Index Scan on requests_opened_on (actual time=0.492..0.492 rows=16715 loops=1)
               Index Cond: ((opened_on >= '2024-07-01'::date) AND (opened_on < '2024-08-01'::date))
 Planning Time: 0.045 ms
 Execution Time: 4.778 ms
(8 rows)

4.8 ms: six times faster, for the same question. >= the first day and < the first day of the next month is the standard way to ask for a month, and it works for timestamps too, where BETWEEN would miss most of the last day. The same goes for lower(name) = 'ashford': an index on name can't help, because it's sorted by name, not lower(name). (You can index the expression itself, CREATE INDEX ... ON towns (lower(name)), when you need to.)

2. The wrong end of a two-column index. An index can cover several columns, sorted by the first, then the second within it, like a phone book sorted by last name, then first name:

SQL

CREATE INDEX requests_town_category ON requests (town_id, category);
EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests WHERE town_id = 5 AND category = 'noise';

OUTPUT

                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Aggregate (actual time=4.831..4.832 rows=1 loops=1)
   ->  Bitmap Heap Scan on requests (actual time=0.923..4.539 rows=14033 loops=1)
         Recheck Cond: ((town_id = 5) AND (category = 'noise'::text))
         Heap Blocks: exact=6264
         ->  Bitmap Index Scan on requests_town_category (actual time=0.488..0.488 rows=14033 loops=1)
               Index Cond: ((town_id = 5) AND (category = 'noise'::text))
 Planning Time: 0.096 ms
 Execution Time: 4.843 ms
(8 rows)

Both columns, used. Now only the second:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM requests WHERE category = 'noise';

OUTPUT

                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=15.513..16.658 rows=1 loops=1)
   ->  Gather (actual time=15.473..16.655 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=14.520..14.520 rows=1 loops=3)
               ->  Parallel Seq Scan on requests (actual time=0.007..13.230 rows=55692 loops=3)
                     Filter: (category = 'noise'::text)
                     Rows Removed by Filter: 277641
 Planning Time: 0.026 ms
 Execution Time: 16.667 ms
(10 rows)

A full scan. A phone book sorted by last name, then first name, is no help finding everyone called Maria. The noise requests are scattered through every town's section. Put the column you filter on most, alone, first. (Two things are true here at once: one in six requests is noise, which is a big share, so even an index on category alone might not be used for this. Lesson to take: check with EXPLAIN, don't assume.)

3. LIKE with a pattern. Lesson 3 said a pattern starting with % can't use a normal index, since the index is sorted from the first letter. Here's what happened when this lesson tried to show it, with an index on category:

SQL

CREATE INDEX requests_category ON requests (category);
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM requests WHERE category LIKE 'stre%' LIMIT 5;
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM requests WHERE category LIKE '%light' LIMIT 5;

OUTPUT

                              QUERY PLAN                              
----------------------------------------------------------------------
 Limit (actual time=0.006..0.008 rows=5 loops=1)
   ->  Seq Scan on requests (actual time=0.006..0.006 rows=5 loops=1)
         Filter: (category ~~ 'stre%'::text)
         Rows Removed by Filter: 24
 Planning Time: 0.108 ms
 Execution Time: 0.013 ms
(6 rows)

                              QUERY PLAN                              
----------------------------------------------------------------------
 Limit (actual time=0.003..0.005 rows=5 loops=1)
   ->  Seq Scan on requests (actual time=0.003..0.004 rows=5 loops=1)
         Filter: (category ~~ '%light'::text)
         Rows Removed by Filter: 24
 Planning Time: 0.017 ms
 Execution Time: 0.008 ms
(6 rows)

A bad test, kept because it teaches something. Both are sequential scans, and both take a hundredth of a millisecond, because of the LIMIT 5. A sixth of the rows are streetlights, so reading from the start of the table finds five within the first 29 rows, and stops. Limit sits on top of the scan and cuts it off early. The planner knew that was cheaper than any index. Without the LIMIT, '%light' really would need every row read. And 'stre%' would too, in this database, for a reason worth knowing: a normal text index in a language-aware collation like en_US.utf8 can't be used for LIKE at all, even with the % at the end. It needs an index made with text_pattern_ops for that. The point stands, though: test the query you'll actually run.

What indexes cost

Indexes aren't free. Each one is stored on disk:

SQL

SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'requests' ORDER BY indexrelname;

OUTPUT

         index          |  size   
------------------------+---------
 requests_category      | 6832 kB
 requests_opened_on     | 7224 kB
 requests_pkey          | 21 MB
 requests_town_category | 6936 kB
(4 rows)

42 MB of indexes on a 57 MB table. The primary key has an index too: every PRIMARY KEY and UNIQUE constraint gets one, which is how the database checks uniqueness quickly. It's the biggest here because every id is different. The others hold the same few values over and over (1,826 dates, six categories), and Postgres stores a repeated value once, with a list of the rows that have it.

And every insert, update and delete has to update every index. Copy 300,000 rows into a table with no indexes, then into one with all five:

SQL

CREATE TABLE requests_bare (LIKE requests INCLUDING DEFAULTS);
INSERT INTO requests_bare (id, town_id, category, opened_on, status) OVERRIDING SYSTEM VALUE
SELECT id, town_id, category, opened_on, status FROM requests WHERE id <= 300000;

OUTPUT

CREATE TABLE
INSERT 0 300000
Time: 106.570 ms

SQL

CREATE TABLE requests_indexed (LIKE requests INCLUDING ALL);
INSERT INTO requests_indexed (id, town_id, category, opened_on, status) OVERRIDING SYSTEM VALUE
SELECT id, town_id, category, opened_on, status FROM requests WHERE id <= 300000;

OUTPUT

CREATE TABLE
INSERT 0 300000
Time: 798.879 ms

LIKE requests copies the table's columns: INCLUDING DEFAULTS without its indexes, INCLUDING ALL with them. The same 300,000 rows took 107 ms without indexes, and 799 ms with them: seven and a half times as long. (OVERRIDING SYSTEM VALUE is Lesson 6's way around a generated id, to keep the same ids.) Tidy up:

SQL

DROP TABLE requests_bare, requests_indexed;

So: index the columns your queries actually filter, join and sort on, and no more. An index nobody uses is pure cost. (That's also why big loads often drop indexes first, load, and rebuild them afterwards.)

Paging: OFFSET, and a faster way

Lesson 3 showed LIMIT 4 OFFSET 4 for page 2 of a list. Here's page 45,001 of a list 20 to a page:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT id, opened_on FROM requests ORDER BY id LIMIT 20 OFFSET 900000;

OUTPUT

                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Limit (actual time=69.066..69.068 rows=20 loops=1)
   ->  Index Scan using requests_pkey on requests (actual time=0.007..54.548 rows=900020 loops=1)
 Planning Time: 0.046 ms
 Execution Time: 69.081 ms
(4 rows)

It uses the primary key's index, and still reads 900,020 rows to return 20. OFFSET can't jump: it has to count past every row it skips. The deeper the page, the slower. Instead, remember where the last page ended, and ask for what comes after it. That's keyset paging:

SQL

EXPLAIN (ANALYZE, COSTS OFF) SELECT id, opened_on FROM requests WHERE id > 900000 ORDER BY id LIMIT 20;

OUTPUT

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Limit (actual time=0.008..0.010 rows=20 loops=1)
   ->  Index Scan using requests_pkey on requests (actual time=0.008..0.009 rows=20 loops=1)
         Index Cond: (id > 900000)
 Planning Time: 0.016 ms
 Execution Time: 0.016 ms
(5 rows)

The same 20 rows, in 0.016 ms instead of 69: over 4,000 times faster. The index jumps straight to 900,001 and reads 20 rows. The trade-off: you can only go to "the next page", not "page 45,001", which is fine for "load more" and infinite scroll, and is how most APIs page through big results.

Foreign keys aren't indexed for you

One last look at the table:

OUTPUT

                          Table "public.requests"
  Column   |  Type   | Collation | Nullable |           Default            
-----------+---------+-----------+----------+------------------------------
 id        | integer |           | not null | generated always as identity
 town_id   | integer |           | not null | 
 category  | text    |           | not null | 
 opened_on | date    |           | not null | 
 status    | text    |           | not null | 
Indexes:
    "requests_pkey" PRIMARY KEY, btree (id)
    "requests_category" btree (category)
    "requests_opened_on" btree (opened_on)
    "requests_town_category" btree (town_id, category)
Foreign-key constraints:
    "requests_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

btree is the kind of index: a balanced tree, the sorted structure behind nearly every index you'll make. And notice that town_id has an index here only because requests_town_category happens to start with it. Postgres doesn't index a foreign key column for you. It indexes the primary key it points to, not the column that points. Look back at Lesson 2's \d leaders: no index on town_id. On a big table, that makes every join on it, and every delete from towns (which has to check for leaders), a full scan. Index foreign key columns yourself, almost always.

To remove the requests table when you're done: DROP TABLE requests;. Reloading larkspur.sql removes it too.

In real systems

Look at the plan before adding an index. EXPLAIN ANALYZE on the slow query tells you whether it's scanning, how many rows it threw away, and whether the estimates were wrong. Adding indexes by guesswork mostly adds write cost.
Postgres tracks which indexes are used. pg_stat_user_indexes, the view used above for sizes, also has idx_scan: how many times each index has been used. One that stays at 0 for weeks is a candidate for dropping.
CivicGrid's tables are small by database standards, a few thousand cities, so most of its queries are fast without much thought. The habits matter anyway: an API that pages with OFFSET is fine at 3,000 rows and a problem at 3 million, and the time to choose is before the table grows.

Spot the problem

Work out what's wrong before you read the explanation.

The index that didn't help. A report runs SELECT ... FROM requests WHERE date_trunc('month', opened_on) = '2024-07-01'. It's slow, so someone adds an index on opened_on. It's exactly as slow as before. Why, and what are the two fixes? The query wraps opened_on in a function, date_trunc, so an index sorted by opened_on can't be used: the database would have to work out every row's month first. EXPLAIN would show a Seq Scan with the date_trunc in its Filter. Fix the query, the better choice: WHERE opened_on >= '2024-07-01' AND opened_on < '2024-08-01'. Or, if the query can't change, index the expression it uses: CREATE INDEX ON requests (date_trunc('month', opened_on)).

Keep this

To	Write
See the plan	`EXPLAIN SELECT ...`
Run it, and see what happened	`EXPLAIN ANALYZE SELECT ...`
Shorter plans	`EXPLAIN (ANALYZE, COSTS OFF) SELECT ...`
Time every query in psql	`\timing on`
Make an index	`CREATE INDEX requests_opened_on ON requests (opened_on);`
An index on two columns	`CREATE INDEX ON requests (town_id, category);`
An index on an expression	`CREATE INDEX ON towns (lower(name));`
A month, index-friendly	`WHERE d >= '2024-07-01' AND d < '2024-08-01'`
Update the statistics	`ANALYZE requests;`
Sizes of a table and its indexes	`pg_size_pretty(pg_relation_size('requests'))`, `pg_stat_user_indexes`
Page fast	`WHERE id > last_seen_id ORDER BY id LIMIT 20`
Make lots of rows	`generate_series(1, 1000000)`

Where you are now

You can read a query plan, tell a scan from an index lookup, make an index the planner will use, and avoid the queries that defeat it. Lesson 10 turns the problem-finding queries from Lessons 3 to 8 into a set of checks that run after every change, the way CivicGrid's do.

Check yourself

EXPLAIN ANALYZE shows 'Rows Removed by Filter: 333150' on each of 3 loops, for 549 rows. What does that tell you?
With an index on opened_on, a query for 80% of the rows still does a Seq Scan. Why?
WHERE extract(year FROM opened_on) = 2024 doesn't use the index on opened_on. Why?
An index on (town_id, category). Which WHERE can't use it well?
Copying 300,000 rows took 107 ms into a bare table and 799 ms into an indexed one. Why?
Why is WHERE id > 900000 ORDER BY id LIMIT 20 so much faster than OFFSET 900000?

Key terms

Index: a sorted copy of one or more columns, pointing to the rows, for finding them without reading the table.
Query plan: the steps the database chooses to run a query. EXPLAIN shows it; EXPLAIN ANALYZE runs it and adds real timings.
Sequential scan: reading every row of a table.
Index scan, bitmap scan: finding rows through an index, one at a time or by collecting their locations first.
Planner: the part of the database that chooses the plan, using statistics.
Statistics: samples of how a table's values are spread, updated by ANALYZE.
Keyset paging: asking for rows after the last one seen, instead of skipping with OFFSET.

Go deeper

The PostgreSQL manual: Using EXPLAIN (opens in a new tab), and Indexes (opens in a new tab).
Try: EXPLAIN ANALYZE SELECT count(*) FROM requests WHERE town_id = 5;. Which index does it use, and why can it?
Try: drop requests_category, then check the sizes again. Then think about whether any of your queries needed it.