Re: Query slow for new participants

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx>:

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,


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux