Some Tips for Resolving SQL Query Performance Anomalies

In this post, I will discuss how to determine whether our SQL queries are running efficiently. Performance issues with queries are common for application developers and can become serious problems in production systems. It’s important to understand and identify the root causes behind slow query execution.

• Monitor shared buffer and cache hit ratio

The shared buffer is a main memory cache PostgreSQL uses for storing data pages. When your relational data fits in the entire memory, it means fewer disk reads and faster queries.

an example for IO & Buffer timins for scan operation

In the example above, we observe that when the dataset is too large to fit into memory or when available memory is insufficient, PostgreSQL must rely heavily on disk I/O. This results in significantly slower query performance and can lead to instability or unpredictability in execution time.

Some tips to track and create a monitor for your system to make an alert or observe:

Buffer Hit Ratio:

• Indicates the proportion of data accessed from memory rather than disk. A high buffer hit ratio (close to 100%) suggests that most operations are served from RAM.

SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

Disk I/O Stats:

• Use tools like iostat or database-specific statistics (pg_stat_io in PostgreSQL to confirm minimal disk activity.

Tune PostgreSQL Settings:

• **shared_buffers**: Allocate a significant portion of RAM for PostgreSQL's shared memory.

shared_buffers = 25% of total RAM  # Adjust based on workload

• Adjust Query Planner Cost Constants for Existing Database Resources

In PostgreSQL, the query planner uses two important cost parameters — seq_page_cost and random_page_cost — to estimate the relative expense of reading data from disk. These parameters guide the planner in choosing the most efficient access strategy for table data, such as whether to perform a sequential scan or use an index. By tuning these values, database administrators can influence how PostgreSQL optimizes queries, especially in environments with specific I/O characteristics or large data volumes.

a example of seq scan to large tables

The sequential scan shown above is a good example of what to avoid when working with large tables. In such cases, use EXPLAIN (ANALYZE, BUFFERS) on your slow query to understand its execution plan. If you consistently observe sequential scans on large datasets, it's a strong indication that indexing, query rewriting, or planner tuning may be needed.

A sequential scan on a large table with low selectivity — where the query only targets a small portion of the data — is often inefficient. If a suitable index exists but is not being used, consider increasing seq_page_cost or decreasing it. random_page_cost to encourage the query planner to favor index scans. These cost parameters can also be adjusted at the transaction level

Tune Settings in PostgreSQL:

BEGIN;
SET LOCAL seq_page_cost = 2.0;
-- your query here
COMMIT;

Also, the PostgreSQL official documentation refers to these parameters as equal if your data fits your database memory.

Tip

Although the system will let you set random_page_cost to less than seq_page_cost,it is not physically sensible to do so.
However, setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence. 
Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be.

see: https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS

• Monitor Database Dead Rows and Vacuum Statistics

In PostgreSQL, it doesn’t immediately remove the old version of the row. PostgreSQL marks the old version as “dead” and creates a new version of the row. These dead rows take up space in the database but are no longer visible to any active transactions. Over time, dead rows accumulate and waste storage, causing performance degradation (because scans and indexes have to skip over them). PostgreSQL has an autovacuum daemon that runs automatically in the background to clean up dead rows regularly.

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM
  pg_stat_all_tables
WHERE
  n_dead_tup > 0
ORDER BY
  n_dead_tup DESC

In the above example, you can track and monitor each scheme and its dead row statistics to manage your optimal vacuum configuration to make. You can also update the vacuum setting in your postgresql.conf file or via the ALTER SYSTEM command. For more information on tuning and modifying, please visit the official documentation.

Tune PostgreSQL Settings:

# Optional autovacuum tuning for better performance
autovacuum_vacuum_scale_factor = 0.05       # Lower threshold to trigger vacuum sooner
autovacuum_analyze_scale_factor = 0.02      # Same for analyze
autovacuum_vacuum_cost_delay = 10ms         # Shorter delay between vacuum steps
autovacuum_vacuum_cost_limit = 200          # More aggressive cleanup
autovacuum_max_workers = 5                  # Allow more workers in parallel

• PostgreSQL Plan Caching and Optimizations

In PostgreSQL, prepared statements are SQL queries with parameters that are planned once and reused. Their performance can vary depending on the parameter values, especially if the query involves joins or casts, since different values can lead to different optimal plans.

For example, if the first query used a very selective filter, the planner may choose an index scan. But if later the parameter value is not selective at all (e.g., returning many rows), the index scan might be the worst possible plan, and the planner won’t change it.

This feature can also be turned off or not enabled for some special queries, because it helps to minimize your plan time for overall operations.

Tune Settings in PostgreSQL:

SET plan_cache_mode = force_generic_plan; # disable to use custom plans

// Dynamically build the query and use EXECUTE. This forces a fresh plan for each execution based on current parameters.
EXECUTE format('SELECT * FROM table WHERE col = %L', param); 

For more information on tuning and modifying, please visit the official documentation.

• Monitor High I/O and Affected Data Space By Queries

In database systems, cascading operations (e.g., parent-to-child relationships) or unbounded query conditions can significantly impact I/O and increase overall system latency. If your monitoring tools or alerts fail to detect and track these operations, they may go unnoticed, ultimately affecting the system’s performance and violating your expected SLA metrics.

If you have the [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) extension enabled, this helps to monitor and alert to your long-running or IO-bound operations.

Track These in PostgreSQL:

SELECT
  query,
  rows,
  calls,
  total_time,
  shared_blks_read,
  shared_blks_hit,
  blk_read_time,
  blk_write_time
FROM
  pg_stat_statements
ORDER BY
  blk_read_time DESC

• shared_blks_read-> blocks read from disk.
• shared_blks_hit-> blocks found in memory.
• blk_read_time-> time spent reading from disk.
• blk_write_time-> time spent writing to disk.

It helps you when looking for queries with high shared_blks_read and high blk_read_time.

SELECT
  relname AS table_name,
  heap_blks_read,
  heap_blks_hit,
  heap_blks_read * 8 / 1024 AS mb_read_from_disk,
  heap_blks_hit * 8 / 1024 AS mb_read_from_cache,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS cache_hit_ratio
FROM
  pg_statio_user_tables
ORDER BY
  heap_blks_read DESC
LIMIT 10;

This query helps to show which tables are causing the most disk reads. If data is being read from disk vs cache.

• Conclusions

Query performance and its inconsistencies can depend on a variety of factors, including data characteristics and system behavior. However, overall performance is strongly influenced by how actively you maintain your data and stay aligned with the evolving needs of your application lifecycle. This post is based on my personal experience, and I welcome any feedback or suggestions you may have.

Thanks for your time, happy reading. Cheers 🍻