We have some sql queries which are rarely executed.
When running them first time after a while they take about 2 minutes, a second call takes only 800ms.
I found out when i restart postgres the querys are still fast. Only when i clear the inode cache with
echo 3 > /proc/sys/vm/drop_caches
the query is slow again. So i started to observe which files are loaded during the query:
inotifywait -mr ~postgres/data/*
which logs on the first time several times:
/var/lib/pgsql/data/base/16414/ ACCESS 119232
it is an 1GB file:
-rwxr-xr-x 1 postgres postgres 1,0G 22. Mär 09:53 /var/lib/pgsql/data/base/16414/119232
i suppose it is a file which contains the index of the table
is it possible to
- Find out which files contain the indexes
- Force the os to keep the files in caches?
