Sample Header Ad - 728x90

Why is PostgreSQL reading from heap and how to improve cache usage?

0 votes
1 answer
192 views
I have an SQL query that executes slowly on PostgreSQL 13.15 with 128GB of memory. The query mainly performs a Bitmap Heap Scan, and I’ve noticed that many reads are coming from the heap instead of the cache. Here’s a link to the query and execution plan. An index that frequently appears in the plan looks like this: create index ix_contacts__addresses_value__normalized on contacts__addresses using gin (company_id, field_name, field_id, value__normalized); My memory settings: • shared_buffers = 32GB • work_mem = 64MB • effective_cache_size = 96GB • maintenance_work_mem = 1320MB Questions: 1. Why is the query reading so much from the heap? 2. How can I configure PostgreSQL to better use memory (shared_buffers, work_mem, and other settings) to avoid unnecessary heap reads? 3. Should I consider modifying the indexes to reduce execution time? I would greatly appreciate any advice on optimizing caching and overall performance.
Asked by Mykola Shved (13 rep)
Sep 13, 2024, 05:41 PM
Last activity: Sep 17, 2024, 03:46 PM