[
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
