Query Tuning Techniques (Part 2)

Introduction

Continuing from the previous article, here are other Query Tuning techniques based on common errors encountered when querying data and the process you can apply:

Optimizing JOIN Statements

  • Ensure columns used for JOIN have Indexes (usually Foreign Key connecting to Primary Key).
    • If Postgres uses a Nested Loop Join with a matching Index, it speeds up finding rows needed for comparison during JOIN
    • If using a Merge Join, it skips the step of sorting two lists before joining
  • Try filtering data using WHERE beforehand or directly inside the ON condition of the JOIN to reduce the row count matching between tables.
    • The result of using JOIN is multiplication: A Join B = A x B
    • Therefore, if conditions can minimize lists A and B, the generated dataset will be as small as possible

Joining Too Many Tables

  • By default, if joining more than 12 tables, Postgres cannot analyze and find the optimal JOIN execution plan because the number of combinations to process is too large
  • At this point, it activates the Genetic Query Optimizer to attempt finding the best possible randomized solution, rather than the globally most efficient solution
  • Solutions
    • Break down the JOIN operation into smaller parts to produce intermediate results using TEMP TABLE or CTE
    • Having fewer tables to JOIN allows Postgres to analyze and operate more efficiently

Using LIKE

  • If you want to search for asymmetrical strings using LIKE in the format {Text}%, the B-Tree Index can work
  • However, if using LIKE/ILIKE with wildcards (%) as follows
    • With %{Text}, no results will be found
    • With %{Text}%, results are found, but the Index will not be utilized
  • Because the B-Tree Index must perform a Full Table Scan, it cannot use the Index to search for starting characters.
  • The solution is to replace it with GIN/GiST Index to utilize Full-Text Search or Trigram, which offer good support for both case-sensitive and case-insensitive LIKE/ILIKE searches

Using OR

If Indexes are already created on columns, we divide into the following scenarios

  • Using OR on the same column
    • In this scenario, whether using OR or IN, Postgres rewrites the query to use OR and processes it similarly
    • However, you should still prefer IN to keep the query concise and clear
  • Using OR across multiple columns
    • In this scenario, if individual Indexes exist on each column (separate Indexes per column, not a Composite Index), Postgres can use Bitmap Index Scan
    • Because OR is used instead of AND across multiple columns, a Composite Index does not work and it must combine with a Heap Scan to recheck conditions rather than relying purely on the Index
    • If the retrieved data volume is too large, Postgres may switch to a Seq Scan instead of using the Index
    • The solution here is to replace it with UNION/UNION ALL for each individual column to use the Index effectively
    • Solution
      • First, create Indexes for columns used with OR, as this keeps the query simple and readable
      • Later, if the retrieved data volume increases significantly during usage, causing Postgres to switch to Seq Scan instead of Bitmap Index Scan
      • Then you should switch to UNION/UNION ALL
    • Example
      • Original query: SELECT * FROM users WHERE status = 'active' OR age > 60
      • Rewritten with UNION ALL: SELECT * FROM users WHERE status = 'active' UNION ALL SELECT* FROM users WHERE age > 60 AND (status != 'active' OR status IS NULL)

Overusing DISTINCT / UNION

  • When using UNION or DISTINCT to eliminate duplicate rows, Postgres performs a heavy operation grouping and sorting the entire dataset (Unique / HashAggregate).
  • If you are certain the data between two sets does not overlap or you accept duplicates, use UNION ALL. The planner will simply append the two datasets without extra overhead for sorting and deduplication.

For example

SELECT user_id FROM order_logs WHERE action = 'click'
UNION
SELECT user_id FROM order_logs WHERE action = 'view';

Can be replaced with

SELECT user_id FROM order_logs WHERE action = 'click'
UNION ALL
SELECT user_id FROM order_logs WHERE action = 'view';

Using NOT IN with Subqueries

  • When using NOT IN, the Postgres planner often selects an extremely slow Sequential Scan execution plan.
  • Moreover, if the subquery returns even a single NULL value, the entire statement yields an empty result
  • You should switch to NOT EXISTS. It is safe with NULL values and enables the planner to use an Anti-Join optimized by Indexes.
    • Example query: SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM departments)
    • Should be changed to: SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.id)

Cleaning and Updating Data

  • Postgres uses MVCC. When performing UPDATE or DELETE, old data is not immediately removed but converted into Dead Tuples
  • Although Auto Vacuum automatically cleans up Dead Tuples when reaching specific thresholds, various reasons can delay this process, leading to inaccurate Postgres statistics
  • This prevents the Planner from choosing optimal paths, such as deciding between Index or Seq Scan
  • Additionally, excessive Dead Tuples consume disk volume and impact performance during Seq Scans (even though these rows are skipped, they must still be loaded to check for Dead Tuples)
  • As a solution, you can proactively execute manual commands
    • ANALYZE table_name: updates statistics so the Planner has enough information to process queries effectively
    • VACUUM table_name: checks that Dead Tuples are no longer in use anywhere, then updates the Free Space Map to allow subsequent INSERT operations to overwrite those Dead Tuples
    • VACUUM ANALYZE table_name: combines both commands above

Detail

Create tables as follows

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'COMPLETED'
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(12,2) NOT NULL
);

Optimizing JOIN Statements

-- Query 1
SELECT 
    c.full_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Query 2
SELECT 
    c.full_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.total_amount > 40000
  • These two queries differ only in the WHERE condition. The Postgres Planner will select the optimal solution when performing a JOIN
  • It selects the smallest tables to join first, so adding more specific conditions reduces dataset size, improves Index efficiency and executes queries faster
  • Query 1 execution process:
    • Seq Scan on orders to create a Hash Table
    • Seq Scan on order_items using Hash Inner Join to join with orders
    • Seq Scan on customers to create a Hash Table and use Hash Inner Join with the previous result
    • Seq Scan on products to create a Hash Table and use Hash Inner Join with the previous result
  • Query 2 execution process:
    • Run Seq Scan on orders with Filter: (total_amount > '40000'::numeric) to create a Hash Table
    • Run Seq Scan on order_items and use Hash Inner Join with orders
    • Index Scan using customers_pkey on customers and use Nested Loop Inner Join with the previous result
    • Index Scan using products_pkey on products and use Nested Loop Inner Join with the previous result

Joining Too Many Tables

WITH OrderTotals AS (
    SELECT 
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY o.customer_id
)
SELECT 
    c.full_name,
    ot.total_spent
FROM customers c
JOIN OrderTotals ot ON c.customer_id = ot.customer_id;

This query calculates the total expenditure per customer using a CTE to break down table JOIN operations

  • OrderTotals represents the result of joining order tables to calculate total expenditure by customer_id
  • Afterward, OrderTotals is joined with the customers table to retrieve full_name
  • If you prefer using a TEMP TABLE, refer to detailed instructions here

Using LIKE

First, use a B-tree Index as follows

CREATE INDEX idx_products_name_btree_pattern ON products(product_name varchar_pattern_ops);

SELECT * FROM products WHERE product_name LIKE 'Logi%';
SELECT * FROM products WHERE product_name LIKE 'logi%';
SELECT * FROM products WHERE product_name LIKE '%853';
SELECT * FROM products WHERE product_name LIKE '%tech%';

SELECT * FROM products WHERE product_name ILIKE 'logi%';
SELECT * FROM products WHERE product_name ILIKE '%853';
SELECT * FROM products WHERE product_name ILIKE '%tech%';
  • Using varchar_pattern_ops creates a B-Tree Index supporting both LIKE and ILIKE
  • The results will be as follows
    • LIKE
      • Logi%: Can use Bitmap Index Scan, the only format supported by B-tree
      • logi%: No results found due to case-sensitivity in LIKE
      • %853 and %tech%: Yields results, but the Index is inactive, falling back to Seq Scan
    • ILIKE for logi%, %tech, %tech%: Yields results, but the Index is inactive









Next, let us examine Full-Text Search and Trigram

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_products_product_name_gin ON products USING GIN(to_tsvector('simple', product_name));
CREATE INDEX idx_products_name_gin_trgm ON products USING GIN(product_name gin_trgm_ops);

-- Query 1
SELECT * FROM products WHERE to_tsvector('simple', product_name) @@ to_tsquery('simple', 'Apple & Phone');

-- Query 2
SELECT * FROM products WHERE product_name LIKE 'Logi%';
SELECT * FROM products WHERE product_name LIKE '%Mouse 853';
SELECT * FROM products WHERE product_name LIKE '%Wireless%';

SELECT * FROM products WHERE product_name LIKE 'Lo%';
SELECT * FROM products WHERE product_name LIKE '%53';
SELECT * FROM products WHERE product_name LIKE '%el%';

SELECT * FROM products WHERE product_name ILIKE 'logi%';
SELECT * FROM products WHERE product_name ILIKE '%mOuSe 853';
SELECT * FROM products WHERE product_name ILIKE '%WIRELESS%';
  • Enable the pg_trgm extension prior to usage
  • idx_products_product_name_gin is a GIN Index for Full-Text Search (tsvector)
  • idx_products_name_gin_trgm is a GIN Index for Trigram Search
  • Query 1: Uses Full-Text Search to find a product_name containing both Apple and Phone
  • Query 2: These queries trigger the idx_products_name_gin_trgm Index
    • LIKE: Supports case-sensitive searches
      • Logi%: Can use Bitmap Index Scan, exact casing required
      • %Mouse 853: Uses Index to find content ending in Mouse 853
      • %Wireless%: Uses Index to find content containing Wireless
      • Although using gin_trgm_ops splits strings into 3-character items, it still works with fewer characters like Lo%, %53, or %el% by automatically adding padding spaces before and after to reach 3 characters for the search."
    • ILIKE: Supports case-insensitive searches, functioning like LIKE while matching lowercase entries












Using OR

SELECT * FROM products WHERE product_id IN(10, 11, 12)
SELECT * FROM products WHERE product_id = 10 OR product_id = 11 OR product_id = 12

Using EXPLAIN shows that both queries are processed identically, but using IN is more concise and readable than OR




To use OR across multiple columns, proceed as follows

CREATE INDEX idx_orders_order_date ON orders(order_date)
CREATE INDEX idx_orders_total_amount ON orders(total_amount)

-- Query 1
SELECT * FROM orders WHERE order_date > '2026-07-01' OR total_amount > 40000

-- Query 2
SELECT * FROM orders WHERE order_date > '2026-07-01'
UNION
SELECT * FROM orders WHERE total_amount > 40000;

-- Query 3
SELECT * FROM orders WHERE order_date > '2026-07-01'
UNION ALL
SELECT * FROM orders WHERE total_amount > 40000 AND (order_date <= '2026-07-01' OR order_date IS NULL);

First, create Indexes for required columns order_date and total_amount. All three queries using OR, UNION, UNION ALL return identical results, but their underlying executions differ

  • Query 1: Postgres uses a Bitmap Index Scan on each column followed by a Bitmap Heap Scan to recheck conditions in the Heap (Recheck Cond: ((order_date > '2026-07-01 00:00:00'::timestamp without time zone) OR (total_amount > '40000'::numeric)))
  • Query 2: The execution splits into two queries
    • Bitmap Index Scan using idx_orders_order_date
    • Index Scan using idx_orders_total_amount
    • Then Append combines rows into the result list, accompanied by an Aggregate step for deduplication
  • Query 3: Operates similarly to UNION but omits deduplication for better performance. Additional boundary conditions total_amount > 40000 AND (order_date <= '2026-07-01' OR order_date IS NULL) ensure accuracy



Overusing DISTINCT / UNION

-- Query 1
SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL;

-- Query 2
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
UNION
SELECT customer_id FROM customers;

-- Query 3
SELECT customer_id FROM orders GROUP BY customer_id
UNION ALL
SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

These three queries produce identical results via different execution methods

  • Query 1: Uses DISTINCT to remove duplicate customer_id entries
  • Query 2: Fetches customer_id from orders, merges with customer_id from customers and eliminates duplicates
  • Query 3: Merges customers who placed orders with those who have not. Using UNION ALL avoids deduplication overhead, so careful query constraints are required



Using NOT IN with Subqueries

-- Query 1
SELECT o.*
FROM orders o
WHERE o.order_id NOT IN (
    SELECT oi.order_id 
    FROM order_items oi
);

-- Query 2
SELECT o.*
FROM orders o
WHERE o.order_id NOT IN (
    SELECT oi.order_id 
    FROM order_items oi
    WHERE oi.order_id IS NOT NULL
);

-- Query 3
SELECT o.*
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.order_id = o.order_id
);

These queries retrieve orders containing product items using NOT IN and NOT EXISTS

  • Query 1: Standard NOT IN usage. If the subquery yields a NULL value, the query returns no rows
  • Query 2: Since NOT IN struggles with NULL values, adding IS NOT NULL ensures the subquery returns no NULLs. This produces correct output but remains inefficient due to lack of Index utilization
  • Query 3: Using NOT EXISTS is more efficient because it leverages Index and Hash Anti Join scans



Cleaning and Updating Data

-- Statement 1
UPDATE order_items SET unit_price = unit_price + 1 WHERE item_id < 100

-- Statement 2
SELECT
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'order_items';

VACUUM order_items
  • Statement 1: Dead Tuples are generated when executing UPDATE/DELETE. Try updating rows and run Statement 2 to observe Dead Tuple counts
  • Statement 2: Displays Live Tuple, Dead Tuple metrics and percentages for a specific table
  • You can execute VACUUM commands to reclaim Dead Tuples and verify the metrics again


Happy coding!

See more articles here.

Comments

Post a Comment

Popular posts from this blog

All Practice Series

Image
Introduction This is a comprehensive page about the technologies I have shared in series format. You can view brief introductions and links to directly access each series you are interested in. In the field of software development, to deploy a product from the initial idea to its release, the standard process typically involves several stages as follows: Database : Designing and implementing the database according to business requirements, storing data during the system's operation. Backend : Handling the main logic of the system, communicating with the database and services. Frontend : Building the interface for users to interact with the system, which could be a desktop, mobile, or web application. This usually includes implementing UI/UX and integrating APIs from the backend. DevOps : Deploying the system for use, which can be done on a server or in the cloud. Testing : Applying testing methods to ensure the product meets the standards for release. Of course, these are just stan...
Read more

Kubernetes Deployment for Zero Downtime

Image
Introduction In Kubernetes (K8s) , a Pod is the smallest resource unit used to run one or more containers during deployment. There are several ways to create a Pod : you can create it directly, use a ReplicationController , or a ReplicaSet . However, the most commonly used resource for managing Pods is the Deployment . When you use a Deployment , it actually creates a ReplicaSet to manage the Pods but comes with many additional benefits that support the deployment process. Some Advantages of Deployment : Ensures Pod Availability : It guarantees that the specified number of Pods are always running according to the configuration, automatically deploying additional Pods if any failures occur. Supports Restart and Undo Deployment : Allows you to easily restart or roll back to previous versions of your Deployment . Zero Downtime Deployment : When updating configurations or scaling Deployments , zero downtime is crucial. This means that new Pods are created while the old Pods are stil...
Read more

Deploying a NodeJS Server on Google Kubernetes Engine

Image
Introduction to GKE Google Kubernetes Engine (GKE) is a managed Kubernetes service provided by Google Cloud Platform , facilitating simple and efficient deployment of Docker images. We only need to provide some configuration for the number of nodes, machine types, and replicas to use. Some Concepts Cluster A Cluster is a collection of Nodes where Kubernetes can deploy applications. A cluster includes at least one Master Node and multiple Worker Nodes . The Master Node is used to manage the Worker Nodes . Node A Node is a server in the Kubernetes Cluster. Nodes can be physical servers or virtual machines. Each Node runs  Kubernetes , which is responsible for communication between the Master Node and Worker Node , as well as managing Pods and containers running on it. Pod A Pod is the smallest deployable unit in Kubernetes . Each Pod contains one or more containers, typically Docker containers. Containers in the same Pod share a network namespace, meaning they have the ...
Read more

Sitemap

Image
A page aggregator designed for easy reference and search, compiling all blog posts for convenient access. Loading all posts...
Read more

React Practice Series

Image
Introduction React is a JavaScript library created by Facebook , often referred to as the most popular frontend framework today. This page aims to gather articles related to ReactJS , covering topics such as theory, features, and commonly used packages in the process of building ReactJS applications. I will update this series with more articles in the future as new ideas for content come up. The articles are arranged in increasing order of difficulty, so if you have time, it's recommended to start from the beginning of the series. This will ensure you grasp the essential knowledge and information needed for the subsequent articles. Here are some key topics in the series that you need to explore to effectively use ReactJS : Fundamental : React Hook, React Reconciliation. State management : redux, mobx, recoil, zustand, xstate, etc. Middleware libraries : redux-thunk, redux-saga, redux-observable, etc. Popular packages : tasnstack query, immer, styled-components, etc. Rendering te...
Read more

Docker Practice Series

Image
Introduction Docker is an open platform for developing, shipping, and running applications. This page is dedicated to compiling articles related to Docker , covering both the theoretical aspects and practical applications of Docker in setting up popular tools essential for software development. I will be updating this series with more articles in the future as new ideas for topics arise. The articles are arranged in increasing order of difficulty, making it easier for you to follow along. If you have the time, I recommend starting from the beginning of the series to ensure that you grasp the necessary knowledge and information needed for the more advanced articles later on. Here are some key topics in the series that you need to explore to effectively use Docker : Basic knowledge Docker commands Docker Compose Building Docker images Performance improvement Integration with CI/CD Once you have a solid understanding of the foundational knowledge, the extended topics (including advanced...
Read more

Helm for beginer - Deploy nginx to Google Kubernetes Engine

Image
Introduction Helm is a package manager for Kubernetes , which simplifies the process of deploying and managing applications on Kubernetes clusters. Helm uses a packaging format called charts , which are collections of files that describe a related set of Kubernetes resources. Key Components of Helm Charts : Helm packages are called charts. A chart is a collection of files that describe a related set of Kubernetes resources. A single chart might be used to deploy something simple, like a memcached pod, or something complex, like a full web app stack with HTTP servers , databases, caches, and so on. Values : Charts can be customized with values, which are configuration settings that specify how the chart should be installed on the cluster. These values can be set in a ` values.yaml ` file or passed on the command line. Releases : When you install a chart, a new release is created. This means that one chart can be installed multiple times into the same cluster, and each can be indep...
Read more

A Handy Guide to Using Dynamic Import in JavaScript

Image
Introduction In JavaScript, when we import a file, the process usually happens synchronously, known as static import. However, as our applications grow, this synchronous loading can lead to slower initial page loads due to larger JavaScript bundles. Moreover, there are times when imports are necessary only under specific circumstances, leading to unnecessary loading times for users who might not even utilize those features. Details Instead of burdening all users with unnecessary imports, dynamic imports come to the rescue. They allow us to load modules or files only when they are needed, thus improving performance and user experience. Dynamic imports are invoked using the import() function and return a promise. When using default exports, the exported data can be accessed through the default field, while other data can be accessed through fields with matching names. Here's how you can use dynamic imports: import ( "ramda" ). then ( module => { const moduleDefaul...
Read more

DevOps Practice Series

Image
Introduction DevOps is a combination of development (Dev) and operations (Ops), aimed at uniting people, processes, and technology to enhance the software development lifecycle. Here are some key aspects of DevOps: Collaboration and Communication: DevOps fosters a culture where development, IT operations, quality engineering, and security teams work together seamlessly. Continuous Integration and Continuous Delivery (CI/CD): These practices automate the integration and delivery of code changes, ensuring faster and more reliable software releases. Infrastructure as Code (IaC): This approach involves managing and provisioning computing infrastructure through machine-readable scripts, rather than physical hardware configuration. Monitoring and Logging: Continuous monitoring and logging help teams to detect issues early and maintain system reliability. Automation: Automating repetitive tasks reduces errors and increases efficiency, allowing teams to focus on more strategic work. By impleme...
Read more

Kubernetes Practice Series

Image
Introduction Kubernetes (often abbreviated as K8s ) is an open-source system designed to automate the deployment, scaling, and management of containerized applications. This page serves as a collection of articles related to Kubernetes (K8s) , including theoretical concepts and practical guides on using Kubernetes to set up essential tools for the software development process. I will continue to update this series with new articles as ideas come to mind, to ensure the series becomes more comprehensive. The articles are arranged in increasing order of difficulty to make it easier for you to follow. If you have time, it's recommended that you start from the beginning of the series to acquire the necessary knowledge and information that will prepare you for the subsequent articles. Key Topics Covered in This Series Basic Knowledge : Fundamental concepts, common commands, etc. Resources : Pod, Deployment, Service, StatefulSet, Ingress, etc. Pod Autoscaler : Horizontal and Vertical sc...
Read more