PostgreSQL slow query detected

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

0 votes

2 answers



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

Another solution if you need both write and read high performance is to disable the ReadACL and move slow queries from the database to Elasticsearch.

A possible solution to move this slow query to ELS would be setting elasticsearch.override.pageproviders to a special CorePageProvider?


Could you execute your query in pgAdmin with EXPLAIN ANALYZE commands and check result on page or other you want ? You have no less than three options to do:

  1. use nuxeo.vcs.optimizations.acl.enabled
  2. moving load to ELS
  3. rebuild SQL query in many ways

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

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.

0 votes

index suggestions by PoWa

FILES:   index.png
0 votes