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
Op di 26 feb. 2019 om 20:08 schreef Michael Lewis <[email protected]>:
>
> Indexes:
>> "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
>> "EnabledIndex" btree (enabled)
>> "ix_companyarticledb_article" btree (articleid)
>> "ix_companyarticledb_company" btree (companyid)
>>
>
> I'd say drop ix_companyarticledb_company since pk_pricedb can be used
> instead even if other queries are only on companyid field, and it will be
> faster for this case certainly since it targets the row you want directly
> from the index without the *"Rows Removed by Filter: 2674361"*
>
> I doubt the default_statistics_target = 100 default is doing you any
> favors. You may want to try increasing that to 500 or 1000 if you can
> afford a small increase in planning cost and more storage for the bigger
> sampling of stats.
>
--
Met vriendelijke groeten,
