Recently I was having a conversation with a DB Enthusiast, and he mentioned that when he was a fresher, he tuned an ETL/reporting query that was running for 8-10 hours via a nightly job by 1/3rd. He went to his manager, saying that he reduced the query execution time, thinking that the manager would be happy. However, the manager said what business impact it is making. What difference will it make if this report is emailed to the boss at 2 AM instead of 8 AM. Eventually, he understood that he tuned the query that was actually not needed, and all the efforts were in vain wasted.
If we put unnecessary indexes in any table that is doing more DMLs operations (INSERT, UPDATE or DELETE) that table will perform badly automatically. Business knows better than DBA whether the table will do more of DML or SELECTS and depending upon that tuning can be done. Of course DBA can check a few things with pg_stat_statements but the DBAs can not tell whether speeding up transactions or speeding up of a SELECT statement improves the business value.
For some applications like Analytics systems, ETL queries are bound to take more time and for some applications like foreign exchange platforms even the query taking a few milliseconds requires tuning. The lesson learnt is whether the query is slow or not, that business will tell. Business is the correct entity to identify whether they want to tune any query or not.
The terms slow query and long-running query are often used interchangeably.
They shouldn’t be.
Understanding the difference is critical because the tuning strategy for each is completely different.
Let’s break it down.
A slow query is a query that takes longer than expected due to inefficiencies and hence it multiplies infrastructure cost unnecessarily.
This usually means below and more:
Example:
|
1 2 3 |
SELECT * FROM orders WHERE customer_id = 10001; |
If there’s no index on customer_id, PostgreSQL will perform a sequential scan on the entire table.
Even if it runs for only 5 seconds, it is slow because it could have run in milliseconds/nanoseconds with proper indexing.
A slow query uses resources inefficiently.
Again, make sure that tuning is actually needed by confirming with the business. If this query is used for a database that stores historical data, where more INSERTs occur than SELECTs, then this index is not needed, provided the business confirms they want to tune this query.
A long-running query runs for a long time — but not necessarily inefficiently.
These queries may:
Example:
|
1 2 3 4 5 6 |
INSERT INTO sales_summary SELECT date_trunc(‘month’, sale_date), SUM(amount) FROM sales WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY 1 |
If the sales table has 500 million rows, this query may run for 15 minutes on the first day of every month — and that’s perfectly fine.
It’s long-running, but not slow.
| Aspect | Slow Query | Long-Running Query |
| Root cause | Poor indexing, stale statistics, or bad SQL. | Large data volume, complex joins, or heavy reports. |
| Fix | Query tuning | Workload planning |
| CPU usage | Often high per row | May be proportional |
| Business justification | Usually none | Often valid |
A slow query is a performance problem.
A long-running query is often a capacity or workload problem.
The real issue occurs when:
Not every 10-minute query is bad.
Not every 2-second query is good.
Context matters. Business justification matters more.
A long-running query becomes dangerous when it:
What can be done
Additionally, Tuning blogs like below can help in tuning
PostgreSQL Performance Tuning Guide: Settings That Make a Difference
SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN
Every slow query is a problem.
Not every long-running query is.
Before killing a query in production, ask:
Is this inefficient — or is it just doing a lot of work?
That distinction defines whether it needs tuning or rescheduling.Business knows the answer better than a DBA in most cases.
