Slow PostgreSQL query responses

[PureBrent]

Subject of the issue

Indexer suffers from slow PostgreSQL query responses with the supported minimum PostgreSQL version. In particular, the case seen with the statement in the reproduction section below responds slow, and slower with more resources.

Your environment

AWS RDS Type vCPU RAM db.r5.xlarge 4 32 GB db.t3.medium 2 4 GB

Steps to reproduce

1. Run the following statement against the indexer SELECT t.round, t.intra, t.txnbytes, t.extra, t.asset, h.realtime FROM txn t JOIN block_header h ON t.round = h.round WHERE t.asset = 22910108 ORDER BY t.round, t.intra LIMIT 1000
2. Explain analyze the statement for query information

Variations 2a. Modify t.asset to another id 2b. Modify machine capability

Expected behavior

Query response times would be returned in a reasonable time and faster with better hardware using.

Actual behavior

Query response is much slower on indexer setups with more capability

Potential resolution

The most efficient resolution may be upgrading the default minimum version of PostgreSQL up from version 11. As reference, AWS now supports version 13 in RDS.

The issue of slowness may be partially due to the txn table having a large number of transactions with the provided id, but they are all recent, so the table is unbalanced. The PostgreSQL version upgrade to 13 will help to address this because of the extended statistics. Both versions 12 and 13 offer improvements for b-tree and storage.

Version 12 "PostgreSQL 12 provides significant performance and maintenance enhancements to its indexing system and to partitioning.

B-tree Indexes, the standard type of indexing in PostgreSQL, have been optimized in PostgreSQL 12 to better handle workloads where the indexes are frequently modified. Using a fair use implementation of the TPC-C benchmark, PostgreSQL 12 demonstrated on average a 40% reduction in space utilization and an overall gain in query performance." - https://www.postgresql.org/about/news/postgresql-12-released-1976/

Version 13 "Building on work from the previous PostgreSQL release, PostgreSQL 13 can efficiently handle duplicate data in B-tree indexes, the standard database index. This lowers the overall space usage that B-tree indexes require while improving overall query performance.

PostgreSQL 13 introduces incremental sorting, where sorted data from an earlier step in a query can accelerate sorting at a later step. Additionally, PostgreSQL can now use the extended statistics system..." - https://www.postgresql.org/about/news/postgresql-13-released-2077/