A PostgreSQL database can feel slow even when the server itself looks healthy.
CPU may not be completely busy. Memory may still be available. Disk space may be fine. Yet one application screen takes several seconds to load, an API becomes slow during peak traffic, or a report suddenly starts taking minutes instead of seconds.
In many cases, the problem is not PostgreSQL as a whole. It is one or more queries doing more work than necessary.
The difficult part is finding which queries matter and understanding why they are slow.
pgAssistant 3.8 makes this investigation more practical by combining query analysis, workload ranking, execution plans, index analysis, and historical workload information. Its 3.8 release also adds Workload Insights, which compares workload measurements over time and shows changes in execution time, call volume, query mix, and the queries responsible for major gains or regressions.
This article walks through a practical way to use pgAssistant 3.8 to find and investigate PostgreSQL query problems.
What Does a PostgreSQL Query Problem Look Like?
A slow query is not always a query that takes five or ten seconds.
A query that takes 20 milliseconds but executes 500,000 times can consume much more database capacity than a query that takes two seconds and runs only a few times.
For example:
Query | Average Time | Calls | Approx. Total Time |
|---|
Report query | 2.0 sec | 20 | 40 sec |
Customer lookup | 30 ms | 50,000 | 1,500 sec |
Order search | 150 ms | 10,000 | 1,500 sec |
The second and third queries may deserve attention before the report query.
This is why workload-level analysis is important.
pgAssistant can rank queries based on execution frequency and overall database impact, helping you move from “which query is slow?” to “which query is creating the most useful optimization opportunity?”
How pgAssistant Helps Find Query Problems
A useful query investigation usually needs more than the SQL text.
pgAssistant can analyze individual SQL statements as well as workloads collected through pg_stat_statements. Its query analysis includes execution plans, joins, scans, sorts, aggregates, buffers, WAL information, row-estimation information, and index-related analysis.
The basic investigation looks like this:
Workload
↓
Find high-impact queries
↓
Inspect query
↓
Inspect execution plan
↓
Check estimates and actual rows
↓
Check indexes and statistics
↓
Make one controlled change
↓
Run the query again
↓
Compare the result
This approach is much safer than creating indexes randomly or changing PostgreSQL configuration parameters because a query happens to be slow.
Start With the Workload, Not the SQL Text
Suppose an application has hundreds of SQL statements.
Looking at each query manually is not practical.
The first question should be:
Which queries are consuming the most database resources?
pgAssistant's query ranking helps prioritize the workload using execution frequency and total database impact.
For example, imagine the following workload:
Query A
Average execution: 900 ms
Calls: 30
Query B
Average execution: 35 ms
Calls: 100,000
Query C
Average execution: 250 ms
Calls: 2,000
Query A looks terrible when viewed individually.
But Query B may be consuming significantly more total database time.
This distinction matters in production systems.
Why Call Volume Matters
A query's impact can be thought of roughly as:
Total execution time
≈
Average execution time × Number of calls
This is a simplified model, but it is useful when prioritizing investigations.
A small optimization applied to a query executed hundreds of thousands of times can have a larger operational effect than a dramatic optimization applied to a rarely executed query.
Check the Execution Plan
Once you identify an important query, the next step is understanding how PostgreSQL executes it.
PostgreSQL's EXPLAIN command shows the plan selected by the optimizer. EXPLAIN ANALYZE also executes the query and reports actual execution information.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC
LIMIT 20;
The important information includes:
Scan type
Estimated rows
Actual rows
Execution time
Number of loops
Buffer hits
Buffer reads
Sort operations
Join operations
Rows removed by filters
pgAssistant's Query Advisor uses execution plans as an important part of its analysis rather than relying only on the SQL syntax. This helps avoid recommendations for indexes that PostgreSQL is already using effectively.
Understanding a Sequential Scan
Consider a plan like this:
Seq Scan on orders
Filter: (customer_id = 42)
A sequential scan means PostgreSQL is reading the table and checking rows against the condition.
That is not automatically a problem.
For a small table, a sequential scan can be the correct choice.
The problem appears when a large table contains millions of rows and the query needs only a small percentage of them.
For example:
orders
---------
10,000,000 rows
Query needs:
---------
20 rows
Scanning millions of rows to find 20 matching rows can become expensive.
This is where index analysis becomes useful.
Finding a Missing or Ineffective Index
Suppose the query is:
SELECT
order_id,
order_date,
total_amount
FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC
LIMIT 20;
And the table contains millions of orders.
An index such as:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);
may provide a better access path.
However, creating the index should not be the first reaction to every sequential scan.
PostgreSQL's documentation points out that index selection depends on the actual workload and that ANALYZE statistics are important for the planner's row estimates.
pgAssistant's Query Advisor also considers predicate types, selectivity, table statistics, planner estimates, existing indexes, and the actual execution plan when evaluating index opportunities.
That is important because the SQL text alone does not tell you whether an index will actually help.
When an Existing Index Is Not Enough
One of the more interesting cases is when PostgreSQL already uses an index but the query is still doing too much work.
Consider:
SELECT *
FROM orders
WHERE customer_id = 42
AND employee_id = 7;
Suppose PostgreSQL produces something similar to:
Index Scan using idx_orders_customer
Index Cond: (customer_id = 42)
Filter: (employee_id = 7)
The database is using an index, but the index only narrows the search by customer_id.
PostgreSQL may still fetch many rows and then apply the second condition.
A more appropriate composite index might be:
CREATE INDEX idx_orders_customer_employee
ON orders (customer_id, employee_id);
Whether this is actually beneficial depends on the data distribution and workload.
This is why pgAssistant examines the existing access path, including index conditions, filters, residual filtering, and row estimates, rather than simply looking for columns appearing in a WHERE clause.
Watch for Bad Row Estimates
Another common query problem is a large difference between estimated and actual rows.
For example:
Index Scan on orders
estimated rows: 10
actual rows: 125000
This is a warning sign.
The planner expected only 10 rows but the operation actually produced 125,000.
That difference can influence later decisions about joins, sorting, aggregation, and memory usage.
PostgreSQL relies on statistics to estimate how many rows satisfy query conditions. These statistics are maintained through ANALYZE and related maintenance operations.
A useful first check is:
ANALYZE orders;
Then run the query again and compare the plan.
Do not immediately assume that inaccurate estimates mean an index is missing.
The underlying issue may instead be stale statistics, data distribution, correlated columns, or another planner assumption.
A Practical Example: Finding a Slow Order Query
Imagine an e-commerce application with this query:
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.total_amount
FROM orders o
WHERE o.customer_id = $1
AND o.status = 'Completed'
ORDER BY o.order_date DESC
LIMIT 50;
The application team reports that the customer order history page is slow.
Step 1: Check Workload Impact
First identify how often the query executes.
Suppose the workload shows:
Calls: 180,000
Average time: 42 ms
Total execution: High
The individual execution time does not look dramatic.
The call volume changes the picture.
Step 2: Inspect the Plan
Run:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.total_amount
FROM orders o
WHERE o.customer_id = 42
AND o.status = 'Completed'
ORDER BY o.order_date DESC
LIMIT 50;
Suppose the plan shows:
Index Scan using idx_orders_customer
Index Cond: (customer_id = 42)
Filter: (status = 'Completed')
Rows Removed by Filter: 24000
This tells us something important.
The existing index is useful, but PostgreSQL is still examining many rows that are later rejected by the status filter.
Step 3: Evaluate a Composite Index
A possible candidate is:
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, order_date DESC);
The exact index should be evaluated against the real workload, table size, write rate, and existing indexes.
Do not blindly deploy every recommendation.
Indexes consume storage and add work to INSERT, UPDATE, and DELETE operations.
Step 4: Test the Change
After creating the index in a suitable environment, run the query again:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.total_amount
FROM orders o
WHERE o.customer_id = 42
AND o.status = 'Completed'
ORDER BY o.order_date DESC
LIMIT 50;
Look for changes such as:
Execution Time
Buffer reads
Rows removed by filter
Scan type
Actual rows
The goal is not simply to make the plan look different.
The goal is to reduce the amount of unnecessary work.
Do Not Ignore Sorts and Joins
Indexes are only one part of query performance.
A query can also become expensive because of:
Large sorts
Hash operations
Nested-loop joins with many iterations
Poor join estimates
Large intermediate result sets
Excessive filtering after a scan
Repeated application-level queries
For example:
SELECT
o.order_id,
c.customer_name
FROM orders o
JOIN customers c
ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';
A useful execution plan might contain:
Hash Join
-> Seq Scan on orders
-> Hash
-> Seq Scan on customers
Whether this is good or bad depends on the number of rows and the actual workload.
A sequential scan of a small customers table may be perfectly reasonable.
This is one reason query optimization should be based on execution evidence rather than rules such as “never use a sequential scan.”
Be Careful With EXPLAIN ANALYZE in Production
There is an important difference between:
EXPLAIN
and:
EXPLAIN ANALYZE
EXPLAIN shows the planned execution path.
EXPLAIN ANALYZE actually executes the statement and collects runtime information. PostgreSQL also documents that EXPLAIN ANALYZE introduces profiling overhead.
This becomes especially important for:
UPDATE ...
DELETE ...
INSERT ...
For example, do not casually run:
EXPLAIN ANALYZE
DELETE FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '5 years';
on a production database.
The statement will execute.
For destructive statements, use an appropriate transaction strategy in a safe environment and understand exactly what the statement will do before collecting runtime plans.
pgAssistant's documentation similarly warns that EXPLAIN ANALYZE executes the statement and recommends using an appropriate database role and care outside development environments.
Use Workload Insights to Check Whether the Fix Worked
This is where pgAssistant 3.8 becomes particularly useful.
Finding a query problem is only half of the job.
After changing an index, query, configuration setting, or maintenance behavior, you want to know what happened afterward.
Workload Insights compares consecutive Collector measurements and can show:
Execution-time changes
Call-volume changes
Query-mix changes
Queries responsible for gains
Queries responsible for regressions
Changes in PostgreSQL configuration
Changes in PostgreSQL versions
Changes in recommendations
For example:
Before change
Query: customer order history
Calls: 180,000
Average time: 42 ms
↓
Composite index deployed
↓
After collection
Calls: 175,000
Average time: 11 ms
That gives the team useful evidence that the workload changed.
But it is important not to overinterpret the result.
A query becoming faster after an index deployment does not automatically prove that the index alone caused the improvement. Other application or database changes may have occurred at the same time.
pgAssistant explicitly treats these historical relationships as evidence rather than automatic proof of causation.
A Repeatable Query Investigation Workflow
For production systems, use a consistent process.
1. Collect a Baseline
Record:
PostgreSQL version
Query workload
Execution time
Call volume
Important configuration
Existing indexes
Table size
2. Find High-Impact Queries
Do not start with whichever query looks slowest.
Look at total workload impact.
3. Inspect the Execution Plan
Use:
EXPLAIN (ANALYZE, BUFFERS)
where it is safe and appropriate.
4. Check Estimates
Compare:
Estimated rows
Actual rows
Large differences deserve investigation.
5. Check Access Paths
Look for:
Seq Scan
Index Scan
Index Only Scan
Bitmap Heap Scan
Also inspect:
Index Cond
Filter
Rows Removed by Filter
6. Check Statistics
If the data has changed substantially, make sure planner statistics are current.
ANALYZE orders;
7. Check Existing Indexes
Before creating a new index, determine whether an existing index already covers most of the workload.
PostgreSQL recommends examining real query workload when evaluating index usage rather than creating indexes without evidence.
8. Make One Significant Change
Avoid changing five things at once.
For example:
Bad investigation:
New index
+ work_mem change
+ query rewrite
+ VACUUM
+ PostgreSQL upgrade
↓
Performance changed
↓
Unknown reason
A controlled investigation is easier to understand:
Baseline
↓
One change
↓
Test
↓
Measure
↓
Decide next step
9. Collect Again
Use pgAssistant 3.8 Workload Insights to compare the new workload with the previous measurement.
10. Continue the Loop
The process becomes:
Observe
↓
Diagnose
↓
Prioritize
↓
Plan
↓
Implement
↓
Collect again
↓
Measure
↓
Repeat
This is the continuous improvement workflow introduced with pgAssistant 3.8.
Common Mistakes When Investigating Query Problems
Creating an Index for Every Slow Query
More indexes are not automatically better.
Indexes require storage and can increase write overhead.
Always verify whether the index addresses a real workload problem.
Looking Only at Average Query Time
Average execution time can hide high call volume.
Always consider both:
Execution time
+
Number of calls
Assuming Every Sequential Scan Is Bad
Sequential scans can be appropriate for small tables or queries returning a large percentage of the table.
Ignoring Statistics
Bad planner estimates can lead to bad plans.
Check statistics before making complicated changes.
Changing Multiple Variables at Once
If you change the query, index, configuration, and hardware simultaneously, it becomes difficult to know which change helped.
Treating a Recommendation as a Guaranteed Fix
A recommendation is a starting point for investigation.
Always validate it against your workload.
Best Practices for Production PostgreSQL
Keep these practices in mind when using pgAssistant for query investigation:
Start with workload impact rather than isolated slow queries.
Inspect the actual execution plan before changing indexes.
Compare estimated and actual rows.
Check existing indexes before creating new ones.
Keep PostgreSQL statistics current.
Test query changes using representative data.
Be careful with EXPLAIN ANALYZE on production workloads.
Make changes incrementally.
Measure performance after the change.
Use historical workload information to identify regressions.
Keep read-only analysis credentials separate from maintenance credentials where possible. pgAssistant documents a dedicated pgassistant_analyze role for normal analysis and a separate maintenance role for operations such as VACUUM, ANALYZE, or statistics resets.
Continue monitoring production systems separately; pgAssistant 3.8's historical analysis is not intended to replace real-time monitoring.
Advantages of Using pgAssistant for Query Investigation
Workload-Based Prioritization
You can focus on queries that have meaningful impact rather than investigating SQL statements randomly.
Execution-Plan-Based Analysis
The Query Advisor uses execution-plan information, helping avoid simplistic recommendations based only on query syntax.
Historical Comparison
Version 3.8 adds Workload Insights for comparing workload measurements over time.
Broader Database Context
Query problems can be considered alongside indexes, statistics, configuration, schema information, and maintenance concerns.
Limitations to Keep in Mind
pgAssistant does not eliminate the need for database engineering judgment.
A recommendation still needs to be evaluated against:
It is also not a replacement for real-time monitoring. pgAssistant's own 3.8 documentation distinguishes historical workload analysis from monitoring of what is happening right now.
The most useful approach is to treat pgAssistant as part of a broader PostgreSQL performance workflow.
Summary
Finding a PostgreSQL query problem is not simply about finding the query with the highest execution time.
A useful investigation starts with the workload and asks which queries have the greatest overall impact. From there, execution plans can reveal whether PostgreSQL is scanning too many rows, using an ineffective access path, performing expensive joins or sorts, or working with inaccurate row estimates.
pgAssistant 3.8 brings these pieces together with query ranking, execution-plan analysis, index recommendations, and historical Workload Insights. The new historical capabilities are especially useful after a performance change because they help teams compare execution time, call volume, query mix, and other database changes over multiple collections.
The important lesson is to avoid tuning by guesswork.
Find the workload problem, inspect the evidence, make one controlled change, measure the result, and then decide what to investigate next. That approach makes PostgreSQL query tuning much easier to understand and much safer to apply in production.
Join the conversation! Your thoughts help the community grow.