Re: Query slow for new participants

[supp...@mekong.be]

Hi,

thank you for your reply.
Yes, I will go through this page.

Regards,
Kim

Op ma 25 feb. 2019 om 17:16 schreef Justin Pryzby :

> On Mon, Feb 25, 2019 at 03:41:18AM -0700, Kim wrote:
> > Is there any way how I can make the queries fast for new participants?
> This
> > is a big problem, because for new participants, speed is even more
> > important.
> >
> > Thank you for your help.
>
> Could you include information requested here ?
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> Justin
>


-- 
Met vriendelijke groeten,

Re: Query slow for new participants

[supp...@mekong.be]

Hello,

Things to Try Before You Post
-> I went through these steps and they did not bring any difference.


Information You Need To Include
Postgres version
"PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"

Full Table and Index Schema
The difference is very bad for the new company,  even on the simplest query

   SELECT * FROM CompanyArticleDB
 WHERE CompanyId = '77'
 AND ArticleId= '7869071'

  Table "public.companyarticledb"
   Column   |Type | Collation |
Nullable | Default
+-+---+--+-
 companyid  | integer |   | not
null |
 articleid  | integer |   | not
null |
 price  | numeric(19,4)   |   |
  |
 contractstartdate  | timestamp without time zone |   |
  |
 contractenddate| timestamp without time zone |   |
  |
 enabled| boolean |   |
  |
 visible| boolean |   |
  |
 sheid  | integer |   |
  |
 inmassbalance  | boolean |   |
  |
 internalwastetype  | character varying(50)   |   |
  |
 buom   | character varying(50)   |   |
  |
 stockunit  | numeric(18,2)   |   |
  |
 priceperbuom   | numeric(19,4)   |   |
  |
 purchaseunit   | numeric(18,2)   |   |
  |
 preventioncounselorid  | integer |   |
  |
 licenseprovided| boolean |   |
  |
 licensevaliduntil  | timestamp without time zone |   |
  |
 authorisationlocationid| integer |   |
  |
 priceagreementreference| character varying(50)   |   |
  |
 interfaceaccountid | integer |   |
  |
 createdon  | timestamp without time zone |   |
  |
 modifiedby | integer |   |
  |
 createdby  | integer |   |
  |
 modifiedon | timestamp without time zone |   |
  |
 createdonsupplier  | timestamp without time zone |   |
  |
 modifiedbysupplier | integer |   |
  |
 createdbysupplier  | integer |   |
  |
 modifiedonsupplier | timestamp without time zone |   |
  |
 newprice   | numeric(19,4)   |   |
  |
 newcontractstartdate   | timestamp without time zone |   |
  |
 newcontractenddate | timestamp without time zone |   |
  |
 newpriceagreementreference | character varying(50)   |   |
  |
 licensereference   | character varying(50)   |   |
  |
 purchasercomment   | character varying(500)  |   |
  |
 reportingunit  | character varying(5)|   |
  |
 articlecode| character varying(50)   |   |
  |
 participantdescription | character varying(500)  |   |
  |
 motivationneeded   | boolean |   |
  |
 photourl   | character varying(500)  |   |
  |
 reviewedshe| boolean |   |
  |
noinspectionuntil  | timestamp without time zone |   |
|
 priority   | boolean |   |
  |
 needschecking  | boolean |   |
  |
 role   | character varying(20)   |   |
  |
Indexes:
"pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
"EnabledIndex" btree (enabled)
"ix_companyarticledb_article" btree (articleid)
"ix_companyarticledb_company" btree (companyid)
"participantarticlecodeindex" btree (articlecode)
"participantdescriptionindex" gin (participantdescription gin_trgm_ops)
Foreign-key constraints:
"fk_companyarticledb_accountsdb" FOREIGN KEY (modifiedby) REFERENCES
accountsdb(id)
"fk_companyarticledb_accountsdb1" FOREIGN KEY (createdby) REFERENCES
accountsdb(id)
"fk_companyarticledb_accountsdb2" FOREIGN KEY (preventioncounselorid)
REFERENCES accountsdb(id)
"fk_companyarticledb_articledb" FOREIGN KEY (articleid) REFERENCES
articledb(id)
"fk_companyarticledb_companydb" FOREIGN K

Re: Query slow for new participants

[supp...@mekong.be]

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 :

>
> 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,