[ https://jira.nuxeo.org/browse/NXP-4843?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Benoit Delbosc reopened NXP-4843: --------------------------------- This bad query plan comes from a bad bet of the query planner with this kind of non scattered data, Reducing the random_page_cost makes the query planner prefer indexscan for this type of situation. SET random_page_cost = 2; EXPLAIN ANALYZE SELECT 1 WHERE EXISTS (SELECT 1 FROM read_acls_cache WHERE users_md5 = '9bc9012eb29c0bb2ae3cc7b5e78c2acf'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=1.06..1.07 rows=1 width=0) (actual time=0.048..0.048 rows=1 loops=1) One-Time Filter: $0 InitPlan 1 (returns $0) -> Index Scan using read_acls_cache_users_md5_idx on read_acls_cache (cost=0.00..55664.21 rows=52517 width=0) (actual time=0.045..0.045 rows=1 loops=1) Index Cond: ((users_md5)::text = '9bc9012eb29c0bb2ae3cc7b5e78c2acf'::text) Total runtime: 0.087 ms (6 rows) Thanks to the pgsql-performance list. To do: rollback, update faq on pgsql tuning > Improve VCS/PostgreSQL read acls cache on large scale storage > ------------------------------------------------------------- > > Key: NXP-4843 > URL: https://jira.nuxeo.org/browse/NXP-4843 > Project: Nuxeo Enterprise Platform > Issue Type: Improvement > Components: Core SQL Storage > Affects Versions: 5.3 GA > Reporter: Benoit Delbosc > Assignee: Benoit Delbosc > Fix For: 5.3.1 > > Original Estimate: 3 hours > Remaining Estimate: 3 hours > > The plpsql function that return the list of read acls from a cache behave > badly when having thousands of read acls. > Some tuning is required to use a better query plan. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.nuxeo.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ ECM-tickets mailing list ECM-tickets@lists.nuxeo.com http://lists.nuxeo.com/mailman/listinfo/ecm-tickets