score:1
This is a "hot" standby. A "warm" standby is constantly replaying WAL records, but does not allow anyone to connect in order to do (read-only) queries against it.
It looks like the query on the standby is setting a massive number of hint bits on table rows which are no longer visible. Or at least, I don't know what else could explain the
dirtied=565,583
on the bitmap heap scan. But once set, they shouldn't need to be set again, so what happens if you repeat the same query back to back? The first execution should clear a path for the second one to be much faster.
It also looks like this table is woefully under-vacuumed. Now it can't be undervacuumed just on the standby of course, because as you note vacuuming can only occur on the master. But being under-vacuumed can have a worse penalty on the standby than it does on the master.
More questions
- PostgreSQL Warm Standby performance really slow
- Postgresql cache (memory) performance + how to warm up the cache
- Django + PostgreSQL best way to improve performance of slow summary aggregation?
- CloudSQL with PostgreSQL very slow performance
- Postgresql sequential scan slow performance on 500 million rows
- Slow uuid_generate performance in postgresql
- PostgreSql using IN statemente with very slow performance
- Really slow performance in postgres
- PostgreSQL MAX() inner query really slow
- Sporadic unusually slow performance for inserting a single row into PostgreSQL database table
- Postgresql max query on big indexed table has slow performance
- Kafka Connect stream and Postgresql DataFileRead Slow Performance
- Slow performance and index not taken into consideration in PostgreSQL
- npgsql / PostgreSQL Very Slow Performance Over LAN
- Slow join performance in Postgresql with large tables
- How to speed up insertion performance in PostgreSQL
- postgresql COUNT(DISTINCT ...) very slow
- PostgreSQL LIKE query performance variations
- PostgreSQL query very slow with limit 1
- Improving OFFSET performance in PostgreSQL
- Slow simple update query on PostgreSQL database with 3 million rows
- Extremely slow PostgreSQL query with ORDER and LIMIT clauses
- PostgreSQL UUID type performance
- PostgreSQL IN operator with subquery poor performance
- PostgreSQL ADD COLUMN DEFAULT NULL locks and performance
- Performance difference between UUID, CHAR, and VARCHAR in PostgreSql table?
- If PostgreSQL count(*) is always slow how to paginate complex queries?
- Javascript serialization and performance with V8 and PostgreSQL
- Why does PostgresQL query performance drop over time, but restored when rebuilding index
- Postgresql - performance of using array in big database
More questions with similar tag
- Can't find suitable driver
- stored procedure for postgres to get next immediate working date from holiday list
- Does PostgreSQL support Active-Active Clustering with DRBD?
- Key is not present in table, but it is | Postgresql, timescaledb
- How can I resolve Memory Quota Exceed and Request Timeout errors on Heroku? Rails Web API
- Running a heroku database command from pgAdmin III? (Harry's Prelaunchr)
- Postgres 10.5: how to link an array of integers to primary keys of separate table?
- How can I improve performance of a many-to-many SQL query?
- Should I have a single database connection or one connection per task?
- Why is my count query on index field slow?
- JDBC driver class not loading for JAVA app when it connects to Postgres in Docker
- Self-Incremental ID for saving in MVC Springframework
- Postgres-compatible Joomla-Components
- Ruby on rails form for postgresql array
- How to split one column into two separated by according to third column in knex.js?
- Return Parts of an Array in Postgres
- select to check if a column does not contain a value in a provided array
- SQL query to get Most Frequently purchased and Most Recently purchased quantity for a customer-product pair
- LEFT JOIN ON string match Very slow
- Which (near) realtime spatial database for 1M+ entries?
- Proper way to insert record with unique attribute
- How to UPDATE or INSERT in PostgreSQL
- Creating Postgres table based on nested JSON?
- How do I write a Django query and include a PostGres function (current_time) in my WHERE clause?
- Sqoop export to postgres with --schema argument
- {"Parameter must have its value set"} dealing with possible null values in NPGSQL
- PostgreSQL - Query to determine which tables are currently being vacuumed?
- A field with precision 10, scale 2 must round to an absolute value less than 10^8
- Postgres database pg_dump - dump file verbosity explanation
- EnterpriseDB Postgres SQL installation failing on Macbook pro 16" M1