DbSchema PostgreSQL starts when you begin the lesson

Lesson 15

Indexes, full table scans and EXPLAIN

For readers who finished part 4 and can query and change the tables of the shop.

After this lesson you can read an EXPLAIN plan, tell a full table scan from an index scan, create an index, and say why PostgreSQL sometimes leaves one unused.
  • How PostgreSQL finds rows
  • EXPLAIN
  • No index on a foreign key
  • Creating an index
  • A table large enough to feel it
  • When an index is not used
  • 4 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read PostgreSQL CREATE INDEX Guide with psql and DbSchema.

The idea

Without an index, PostgreSQL reads every row

Left: a Seq Scan reads orders row by row, all 2,000 of them, testing each against customer_id 42. Right: an index on customer_id lists the values in order, and its 5 entries for 42 lead straight to the 5 matching orders.

To find customer 42's orders, PostgreSQL can read the whole table and test each row: a full table scan. An index is a sorted copy of one column with a pointer to each row, so the matching rows are found directly.

Try it

EXPLAIN shows how a query will run

EXPLAIN in front of a query returns its plan instead of its rows. Primary keys always have an index.

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
explain select * from orders where order_id = 42;

Index Scan using orders_pkey: PostgreSQL looks order 42 up in the primary key's index.

Try it

A foreign key gets no index of its own

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
explain select * from orders where customer_id = 42;

Seq Scan on orders, with a Filter: every order is read and tested. PostgreSQL builds an index for a primary key, but never for a foreign key.

Try it

Create the index, and the plan changes

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
create index on orders (customer_id);
explain select * from orders where customer_id = 42;

A Bitmap Index Scan on the new index finds the 5 orders, and a Bitmap Heap Scan reads only those rows.

The index stays until you reset the database. PostgreSQL names it for you, here orders_customer_id_idx.

Setup

Make a table large enough to feel the difference

The shop is small: every query on it takes under a millisecond. Each button below runs this SQL with its number of rows, dropping the table first:

drop table if exists page_views;
select setseed(0.42);
create table page_views as
select g as view_id,
       1 + floor(random() * 500)::int as customer_id,
       timestamp '2025-01-01' + random() * interval '600 days' as viewed_at
from generate_series(1, 200000) g;
analyze page_views;

setseed makes random() return the same numbers for every reader. The million rows take a few seconds and add about 50 MB to this page's database.

Try it

EXPLAIN ANALYZE runs the query and measures it

explain analyze runs the query and reports what happened: the rows each step read, and the time it took.

explain analyze
select * from page_views where customer_id = 42;

Seq Scan, with Rows Removed by Filter: 199,626 at 200,000 rows. Fill another size and run it again: the scan grows with the table.

Try it

Index the lookup and measure again

create index on page_views (customer_id);
explain analyze
select * from page_views where customer_id = 42;

A Bitmap Heap Scan now reads only customer 42's rows, and the Execution Time drops. A new fill drops the table, and its index with it.

Watch out

On a small table, PostgreSQL ignores the index

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
create index on products (category);
explain select * from products where category = 'Audio';
QUERY PLAN
Seq Scan on products (cost=0.00..1.32 rows=3 width=37)
Filter: (category = 'Audio'::text)

The index exists, and PostgreSQL does not use it. All 26 products fit in one page of the table, so reading that page is cheaper than reading the index first. It decides the same way for a value that most rows have.

In DbSchema

Create an index from the table dialog

The DbSchema dialog Create Index on Table orders, with the name orders_customer_id_idx, the type Index selected, and the column customer_id in its list
The index of this lesson, in the index dialog of DbSchema.

In DbSchema, double-click a table's header, open the Primary Key / Unique / Indexes tab and press Add: the dialog asks for the type, here Index, and the columns. The steps are in the documentation.

Download DbSchema

The Community Edition is free. Every download starts with a 15-day trial of all features.

Exercise 1 of 4

Without an index on customer_id, how does PostgreSQL read orders for select * from orders where customer_id = 42? If you created the index on slide 5, reset the database first, from the Tables button.

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
explain select * from orders where customer_id = 42;

Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.

Exercise 2 of 4

Create an index that lets PostgreSQL find a customer's orders without reading the whole table.

Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
create index on orders (customer_id)

    






    
Exercise 3 of 4

    




With an index on products (category), EXPLAIN still shows a Seq Scan for category = 'Audio'. Why?



    

    

        
        
        
        
    

    
Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.

    





    
Exercise 4 of 4

    




Fill page_views with 200,000 rows, with the button on slide 6, and run explain analyze on the lookup of customer 42 without an index. How many rows did PostgreSQL read and throw away?


explain analyze
select * from page_views where customer_id = 42;



    

    

        
        
        
        
    

    
Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.