topic Re: PostgreSQL slow query detected in Nuxeo Forum

PostgreSQL slow query detected

Henri_Jacob — Thu, 31 Aug 2017 15:40:30 GMT

When load testing my (LTS2015 HF27 + PosgreSQL 9.6 + ElasticSearch 1.x) platform with Tsung , i get a (very) slow query as shown below .

Even if create the index as suggested by PoWa : CREATE INDEX hierarchy_mixintypes_idx
/> ON public.hierarchy
/> USING gin (mixintypes COLLATE pg_catalog."default");

Regards, Henri

Re: PostgreSQL slow query detected

Henri_Jacob — Thu, 31 Aug 2017 15:52:34 GMT

index suggestions by PoWa

Re: PostgreSQL slow query detected

Florent_Guillau — Thu, 31 Aug 2017 16:08:57 GMT

Your query contains NX_ACCESS_ALLOWED which means you disabled the ACL optimizations. It's unavoidable that with a largish amount of data any query will become slow. See https://doc.nuxeo.com/nxdoc/read-acls/

Make sur you define nuxeo.vcs.optimizations.acl.enabled=true and follow the guide in the page above (REBUILDING READ ACLS) to make sure the indexes needed are recomputed after you do the switch.

Re: PostgreSQL slow query detected

Henri_Jacob — Fri, 01 Sep 2017 07:43:16 GMT

We defined nuxeo.vcs.optimizations.acl.enabled=false in order to minimize the ACL write time, as recommended in https

Re: PostgreSQL slow query detected

adam_bo_ — Sat, 02 Sep 2017 17:32:01 GMT

Could you execute your query in pgAdmin with EXPLAIN ANALYZE commands and check result on page https