Postgresql – How to place connection metadata in order to trace the queries on RDS performance insights fot the postgresql db

Comparing details from the two execution plans provided:

• Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
• Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
• Slow plan uses the version 70 cardinality estimator
• Fast plan uses the version 120 cardinality estimator
• Slow plan has estimated available DOP of 2 (half the visible CPUs)
• Fast plan has estimated available DOP of 4 (half the visible CPUs)
• Estimated memory grant available is also different between the two
• Parameter values used during compilation are identical

With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);

at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.

Related reading:

Forcing a Parallel Execution Plan
Different Plans on UAT and PROD
Multiple Plans for an "Identical" Query
Different Plans for "Identical" Servers

The most important information in addition to the connection state of the hung queries would be whether they are in any particular wait event.

You can get that information by running “SELECT state, wait_event_type, wait_event FROM pg_stat_activity” (you may need to collect this in a cron job to get the data over time)

Note that RDS also shows this information in “Performance Insights”, if you have it enabled.

Since you are on an older Postgres patch release, I would also investigate an upgrade of the minor version to see if that fixes the issue you are seeing.