How do I improve the performance of long-running queries for my Amazon RDS for PostgreSQL or Amazon Aurora DB instance?

Resolution

Identify long-running queries

To use the pg_stat_activity view to list queries that are running for more than 5 minutes, run the following query:

SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    usename,
    datname,
    client_addr
FROM pg_stat_activity 
WHERE state = 'active'
AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY duration DESC;

Note: The preceding query returns the duration, query text, user context, and process ID that's required to shut down the instance.

To use the pg_locks view to identify potential blocking scenarios, run the following query:

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement,
    now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

If you have nested locks, then run the following query to identify potential blocking scenarios instead:

WITH lok AS (
    SELECT array_cat(array_agg(pid),
    array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids 
    FROM pg_locks
    WHERE NOT granted
)
SELECT p.pid, p.usename, p.datname, p.state,
    p.wait_event_type || ': ' || p.wait_event AS wait_event,
    current_timestamp-p.state_change time_in_state,
    current_timestamp-p.xact_start time_in_xact,
    l.relation::regclass relname,
    l.locktype, l.mode, l.page, l.tuple,
    pg_blocking_pids(l.pid) blocking_pids,
    (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
    coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),p.pid||'.0') lock_depth,
    p.query
FROM pg_stat_activity p
    JOIN lok s on (p.pid = any(s.pids))
    LEFT OUTER JOIN pg_locks l on (p.pid = l.pid and not l.granted)
ORDER BY lock_depth;

Monitor long-running queries

Complete the following steps:

1. Run the following query to create the pg_stat_statements extension in your database:

   CREATE EXTENSION pg_stat_statements;

2. Run the following query to get aggregate statistics on query performance:

   SELECT 
       query,
       calls,
       total_time,
       mean_time,
       rows
   FROM pg_stat_statements
   ORDER BY total_time DESC
   LIMIT 10;

   Note: The preceding query shows the top 10 queries by total execution time, including the number of calls, average time for each call, and affected rows.

3. Use Performance Insights to monitor DB load and identify performance bottlenecks.
   Note: On the Performance Insights dashboard, you can filter and group loads by waits, SQL statements, hosts, or users.

4. Set up Enhanced Monitoring to get metrics on your DB instance's resource usage, processes, and operating system (OS).

5. Create an Amazon CloudWatch alarm so that you can get automated alerts and trend analyses.
   Note: To monitor performance, use CloudWatch metrics such as DatabaseConnections, CPUUtilization, FreeableMemory, ReadLatency, WriteLatency, Read/Write IOPS, and Read/Write Throughput.

Important: Performance Insights will reach its end of life on June 30, 2026. You can upgrade to the Advanced mode of Database insights before June 30, 2026. If you don't upgrade, then DB clusters that use Performance Insights will default to the Standard mode of Database Insights. Only the Advanced mode of Database Insights will support execution plans and on-demand analysis. If your clusters default to the Standard mode, then you might not be able to use these features on the console. To turn on the Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon RDS and Turning on the Advanced mode of Database Insights for Amazon Aurora.

Use query logging to find slow performing queries

Turn on database query logging. Then, configure database parameters, such as log_min_duration_statement, log_statement, and log_lock_waits, to capture queries, lock waits, checkpoints, and other database operations.

Set up auto_explain monitoring

Use auto_explain to automate execution plan logging for slow queries. For more information, see auto_explain — log execution plans of slow queries on the PostgreSQL website.

Improve the performance of long-running queries

Complete the following steps:

To analyze your query plans and print the query execution plan, run the following EXPLAIN query:

EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;

Note: To identify inefficient operations, review the output that shows how PostgreSQL plans to run the query.

To improve the performance of long-running queries, take the following actions:

• Based on the EXPLAIN output, create indexes on columns that you frequently use in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Verify that JOIN operations are performed on indexed columns, and denormalize data to reduce the use of complex JOIN operations.
• For tables with millions of rows, use table partitioning to improve query performance.
• Regularly run ANALYZE and VACUUM operations on your tables so that the query planner has up-to-date statistics and the dead tuples are removed.
• Use materialized views for complex queries that you frequently run but don't need real-time data.
• Use appropriate data types for your columns to minimize storage and improve query performance.
• Simplify complex queries, or use Common Table Expressions (CTEs) to improve query readability and performance.

Important: Regularly monitor and tune your database performance, and review and reduce your most resource-intensive queries.

Set up hints

Install the pg_hint_plan extension

Complete the following steps:

1. Add pg_hint_plan to the shared_preload_libraries parameter of your database parameter group. Use the pg_hint_plan extension to modify the execution plans of your queries. For more information, see pg_hint_plan on the GitHub website.
2. Create the pg_hint_plan extension in your database.

Add the hints as comments in the format, /*+ hint */. When you use the hints to specify join methods, scan methods, and planner parameters, you don't alter the core SQL syntax. You can troubleshoot performance, test query behavior, or apply temporary workarounds in production environments.

Edit the database parameter group

Complete the following steps:

1. Open the Aurora and RDS console.

2. In the navigation pane, choose Parameter groups, and then select the parameter group of your DB instance.

3. Choose Actions, and then choose Edit.

4. In the search box, enter shared_preload_libraries.

5. In the Values field, add pg_hint_plan to the list. Use a comma to separate items in the list of values.

6. Chose Save changes.

7. Restart your DB Instance.

8. To verify that pg_hint_plan initialized after your instance is available, run the following command to connect to your RDS for PostgreSQL instance:

   SHOW shared_preload_libraries;

9. After pg_hint_plan initializes, create the pg_hint_plan extension:

   CREATE EXTENSION pg_hint_plan;

   Note: To identify available pg_hint_plan versions, use the pg_available_extensions or pg_available_extension_versions system views.

If you can't edit the query, then create a hint table. For more information, see The hint table on the GitHub website. The extension owner owns the hint table and has the same default permissions as during CREATE EXTENSION. Hints in the hint table take priority over the hints that you specify in comments.

Example hint_plan.hints table:

postgres=# SHOW pg_hint_plan.enable_hint;
 pg_hint_plan.enable_hint
 on
(1 row)
 
postgres=# SHOW pg_hint_plan.enable_hint_table;
 pg_hint_plan.enable_hint_table
 on
(1 row)
 
-- hint_plan.hints defines the hint of using SeqScan(account) when executing EXPLAIN (COSTS false) SELECT * FROM account WHERE account.id = 50
 
postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) VALUES('EXPLAIN (COSTS false) SELECT * FROM account WHERE account.id = ?;', '', 'SeqScan(account)');
INSERT 0 1
 
postgres=# update hint_plan.hints set hints='IndexScan(account a_pk)' where id=1;
UPDATE 1

Note: The hint in the preceding example applies to sessions that you connect to from the psql command line interface (CLI). An empty string is a session with an application_name.

To retrieve the query ID, use pg_stat_statements or EXPLAIN VERBOSE.

Related information

Monitor Amazon RDS for PostgreSQL and Amazon Aurora for PostgreSQL database log errors and set up notifications using Amazon CloudWatch

Best practices for working with PostgreSQL

Performance tips on the PostgreSQL website