Is there any way to check if index is loaded to memory?
Add a comment
|
3 Answers
Check the contrib module pg_buffercache
After installation you can use this query to see if the table and index are in the buffercache:
SELECT
DISTINCT
relname
FROM
pg_buffercache
JOIN pg_class USING (relfilenode)
WHERE
relname IN('your_tablename','your_index_name');
9 Comments
offline15
I checked this, but it doesn't help. Database can use the index but read it from hard disk every time instead of memory.
Frank Heikens
Show us your relevant datamodel, query and the result from EXPLAIN ANALYZE. Without this information it's impossible to help you.
offline15
Why should this matter? I know the name of the index that query is using and checked with EXPLAIN ANALYZE that postgresql is actually use it, I just want to check that when it scans this index it doesn't reading that from hard disk every time
Frank Heikens
Ah, my mistake. Are you 200% sure the complete index is read from disk when it's already in memory? This would be very strange, that would make memory useless. And it's not. Did you check the page numbers (relblocknumber) of the index as well?
offline15
I'm sure that is reading from hard disk because of high IO load when I run the query. The settings of shared_buffers is 2GB, effective_cache_size is also 2GB, the index is only 350MB, so it should fit to the memory. I don't understand why Postgres keep reading from hard disk..
|
Note that PostgreSQL can't execute queries just based on the index data. It has to visit the data in order to determine what information is visible from the perspective of the query; that information is only in the rows themselves, not in the index. It's quite possible the index is all in RAM, but you're still seeing heavy I/O because the table doesn't fit there.
Comments
Use PgFincore for that.
Note that this question really has a two step solution because PostgreSQL has a two-level caching mechanism. pg_buffercache is the right tool for examining the PostgreSQL shared buffer cache. pgfincore is the right tool for examining the operating system's cache.
1 Comment
wutzebaer
select * from pgfincore('tablename'); returns >>ERROR: fincore(91, 0, 1073741824, 0x22a83a0): Invalid argument<< where pgfadvise_willneed runs without error, do you know whats wrong?