Query Tuning Techniques

Introduction

These are Query Tuning techniques based on common errors encountered when querying data and the process you can apply:

Avoid Using SELECT *

When you use queries like this

SELECT * FROM users WHERE id = 100
SELECT username FROM users WHERE id = 100

If there is no Index, Postgres will have to use Heap Scan to load the corresponding entire data page into RAM
At this point, whether you use SELECT * or SELECT name, Postgres handles it the same way
But the difference starts here, if you only SELECT the necessary columns, Postgres only needs to process those columns without spending additional CPU cost to process all columns as with SELECT *
- If any of the requested columns are too large and stored in the TOAST Table, it incurs additional I/O and data decompression costs
- At the same time, if you deploy a Web Server to respond with this data to FE, larger amounts of data will consume more network bandwidth, RAM to parse JSON and take longer to process
Solution
- Only retrieve the specific columns needed
- Add appropriate Indexes for the required columns

Use Appropriate Indexes

If a query is executed frequently, add suitable Indexes to the columns, in which case
- Postgres can use Index Only Scan instead of Seq Scan
- If the required columns are not all in the Index, Postgres will still rely on conditions to use Index Scan, scanning the Index first and then using TID to fetch data from the Heap, which is still much faster than a standard Seq Scan
To let Postgres decide to use an Index instead of Seq Scan, it relies on several factors such as
- Whether the number of rows to retrieve is small or large for the Optimizer to calculate costs and choose the optimal method
- Whether the created Index is included in condition clauses like WHERE, JOIN, ORDER BY and GROUP BY
You can read articles about different types of Index to understand their characteristics and use them appropriately

Use Functions on Indexed Columns

How you create an Index dictates how you must query with the exact condition so Postgres can utilize the corresponding Index
Therefore, using a Function on an indexed column alters the value of that column, preventing the Index from working
- For example, using SELECT * FROM orders WHERE YEAR(created_at) = 2000
- Even if created_at has an Index, it cannot be used
Solution
- Keep the original column and shift the logic to the Literal Value side as follows SELECT * FROM orders WHERE created_at >= '2000-01-01' AND created_at <= '2000-12-31'
- You can reverse it by applying the Function to the value side so the Index still works like WHERE name = lower('Your Name')
- Use an Expression Index

Direct Calculations on Condition Columns

This is similar to using a Function on a comparison column, Postgres cannot calculate the value to match against the Index data
The solution is to move all calculations to the constant side
- For example, SELECT * FROM products WHERE price * 0.9 < 100
- Change it to SELECT * FROM products WHERE price < (100 / 0.9)

Use ORDER BY or LIMIT

Using ORDER BY
- Using ORDER BY on a column without an Index forces a Seq Scan followed by a Sort on that dataset
- If the column has an Index, it breaks down into several query scenarios
  - SELECT created_at FROM posts ORDER BY created_at
    - If you only need columns present in the Index, Postgres just uses Index Only Scan to retrieve data
    - Whether sorting ASC or DESC, it works as expected
  - SELECT * FROM posts ORDER BY created_at
    - When fetching all columns or other non-indexed columns, Postgres calculates the Cost to choose between Index Scan and Seq Scan
    - Using Index Scan means scanning the Index first and using Random I/O to read data in the Heap
    - Using Seq Scan means fetching data in the Heap and performing a Sort on the column
    - Depending on the amount of data to fetch, Postgres chooses the appropriate solution
Using LIMIT: points to keep in mind
- Even if you only SELECT columns in the Index without using ORDER BY or WHERE, Postgres will choose Seq Scan
- Because without additional constraints, reading sequentially from the Heap to get the first few rows is faster and simpler than using an Index
Using ORDER BY and LIMIT
- Without an Index, it is the worst-case scenario because it performs a Seq Scan to load all data, sorts it and then uses LIMIT to get the specified row count, discarding unused rows and wasting Sort cost
- If an Index exists, it breaks down into the following scenarios
  - If retrieving only columns in the Index, it uses Index Only Scan
  - If retrieving additional columns outside the Index
    - If LIMIT is not too large and can be processed in work_mem, Postgres still chooses Index Scan (scanning the Index first, then Heap Scan)
    - If LIMIT is too large, Postgres cost estimation may determine Seq Scan+Sort is more efficient

Implicit Type Mismatch

We categorize this into the following cases

Column Transformation
- Occurs when you use a Function to change the data type
- Using a Function on a column disables the Index as discussed earlier
- The solution is similar: shift logic, use a Function on the value, or create an Expression Index
Implicit Casting
- Occurs when comparing 2 different data types, forcing Postgres to automatically cast one of them
  - If Postgres automatically casts the Value side, the Index still works
  - If Postgres automatically casts the Column side, the Index cannot work
- This depends on whether the 2 data types can be cast for comparison and their type precedence

Detail

Create Tables and Indexes as follows

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15) UNIQUE,
    balance NUMERIC(10,2),
    dob DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_balance ON users (balance);
CREATE INDEX idx_users_created_at ON users (created_at);

Then seed data like this


INSERT INTO users (username, email, phone, balance, dob, created_at)
SELECT 
    'user_' || gs.i AS username,
    'user_' || gs.i || '@example.com' AS email,
    '09' || LPAD((FLOOR(RANDOM() * 100000000))::text, 8, '0') AS phone,
    ROUND((RANDOM() * 5000)::numeric, 2) AS balance,
    '1970-01-01'::DATE + (RANDOM() * (DATE '2005-12-31' - DATE '1970-01-01'))::INT AS dob,
    NOW() - (RANDOM() * INTERVAL '365 days') AS created_at
FROM generate_series(1, 10000) AS gs(i);

Next, we analyze scenarios with the following queries