Hello All,
Thank you very much for your help. You have really helped me out!
The query is now as fast as the others.
The indexes ix_companyarticledb_article and ix_companyarticledb_company are removed.
The parameter for default_statistics_target was set to 1000
ANALYZE was performed on the database
I am so happy this worked out.
The pg_buffercache extension is now installed, and I will be working with it the coming days to improve my settings.
First time I ran the query (evening, not high peak usage)
SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;
"pk_pricedb" "1479655"
"companyarticledb" "1378549"
"articledb" "780821"
"pricedb" "280771"
"descriptionindex" "138514"
"ix_pricedb" "122833"
"pk_articledb" "47290"
"EnabledIndex" "29958"
"strippedmanufacturernumberindex" "25604"
"strippedcataloguenumberindex" "24360"
How can I see if the whole DB is kept in RAM?
How to define the best setting for work_mem ?
Thanks for your help!
Regards,
Kim