From: | "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com> |
---|---|
To: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Postgres index usage |
Date: | 2024-08-07 17:06:19 |
Message-ID: | BL0PR03MB4001C72C153C481533CBFCDAFAB82@BL0PR03MB4001.namprd03.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I've found multiple postings out there saying you can query pg_stat_all_indexes and look at idx_scan to know if an index has been used by queries. I want to be 100% sure I can rely on that table/column to know if an index has never been used.
I queried that table for a specific index and idx_scan is 0. I queried pg_statio_all_indexes and can see idx_blks_read and idx_blks_hit have numbers in there. If the index is not being used then what it causing idx_blks_read and idx_blks_hit to increase over time? I'm wondering if those increase due to DML on the table. Could anyone please confirm I can rely on pg_stat_all_index.idx_scan to know if queries are using an index and the increases over time in idx_blks_read and idx_blks_hit in pg_statio_all_indexes would be from DML (or possibly vacuum or other things)?
Thanks in advance.
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
From | Date | Subject | |
---|---|---|---|
Next Message | Dirschel, Steve | 2024-08-07 17:23:35 | RE: Postgres index usage |
Previous Message | Pavel Stehule | 2024-08-07 10:00:14 | Re: proposal: schema variables |