From: | "support(at)mekong(dot)be" <support(at)mekong(dot)be> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | Justin Pryzby <pryzby(at)telsasoft(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Query slow for new participants |
Date: | 2019-02-26 21:39:05 |
Message-ID: | CALJ2KGVpdy_XK==sedEQWb+6AJHMtX1X5rPZDS-fY=X9d-sopw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <mlewis(at)entrata(dot)com>:
>
> 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,
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2019-02-27 07:58:17 | Re: Query slow for new participants |
Previous Message | Michael Lewis | 2019-02-26 19:08:51 | Re: Query slow for new participants |