2

I have a Postgres 13.10 database running on AWS RDS, with a primary instance and a replica. The replication is managed by AWS - it's not logical replication I manage myself.

The replica is configured with hot_standby_feedback=1. Queries on the replica always run with a statement timeout of 60s to ensure they're not canceled if they conflict with WAL replication.

The database has a widgets table that is ~1Tb on disk, including a number of bloated indexes. The table has a relatively high update rate as part of a transactional system. We're aware the size is larger than recommended and are working on addressing that. The table predates declarative partitioning and it is a parent table with a single child in a table inheritance hierarchy.

When I run the following query on the primary and the replica, I find that the replica does significantly more Heap Fetches than the primary. The primary is slower in these tests because some blocks are read from disk, but that's not concerning us.

At the time of this particular test, the replica required ~2.5x the number of heap fetches. As a result, responding to the query touches ~800k buffers on the primary but ~2.1M buffers on the replica.

I think this suggests the visibility map on the replica isn't helping as much as on the primary?

Can anyone explain why the Heap fetches are so different on the two instances? Are changes to the visibility map replicated to the replica as the table is updated?

Additional observations that are interesting:

  1. running REINDEX ( VERBOSE ) INDEX CONCURRENTLY index_widgets_foo on the primary results in the heap fetches numbers on the replica dropping to about the same as on the primary, Great! Except then as the table accumulates UPDATES on the primary the Heap fetches numbers start to slowly diverge again
  2. Running a vacuum on the table (on the primary) also seems to bring the Heap fetches numbers close together (for a while).
explain (analyze, buffers) SELECT queue, COUNT(*)
FROM ONLY widgets
WHERE account_id = 123
  AND thing_id IS NULL
  AND type = 'foo'
  AND destroyed_at IS NULL
GROUP BY queue;

primary

                                                                                    QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=40780.06..41327.15 rows=54709 width=29) (actual time=63238.961..63239.240 rows=62 loops=1)
   Group Key: queue
   Batches: 1  Memory Usage: 1561kB
   Buffers: shared hit=600937 read=218122 dirtied=7703
   I/O Timings: read=61192.139
   ->  Index Only Scan using index_widgets_for_metrics_with_legacy_queue on widgets  (cost=0.56..39897.92 rows=117618 width=21) (actual time=0.019..63235.662 rows=16916 loops=1)
         Index Cond: (account_id = 123)
         Heap Fetches: 1067274
         Buffers: shared hit=600937 read=218122 dirtied=7703
         I/O Timings: read=61192.139
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.530 ms
 Execution Time: 63240.040 ms

Replica

                                                                                    QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=40780.06..41327.15 rows=54709 width=29) (actual time=2065.951..2066.156 rows=59 loops=1)
   Group Key: queue
   Batches: 1  Memory Usage: 1561kB
   Buffers: shared hit=2116371
   ->  Index Only Scan using index_widgets_for_metrics_with_legacy_queue on widgets  (cost=0.56..39897.92 rows=117618 width=21) (actual time=0.649..2062.977 rows=16642 loops=1)
         Index Cond: (account_id = 123)
         Heap Fetches: 2541914
         Buffers: shared hit=2116371
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.475 ms
 Execution Time: 2066.303 ms
2
  • 2
    Ah! I think it might be this postgrespro.com/list/thread-id/… - "At the same time, some queries (index scans) could be much slower on hot standby rather than on the primary one. It happens because the LP_DEAD index hint bits mechanics is ignored in index scans during recovery" Commented Aug 21, 2023 at 2:33
  • Yes, your comment is the answer
    – jjanes
    Commented Aug 21, 2023 at 12:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.