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
Last activity: Sep 17, 2024, 03:46 PM