[
https://jira.nuxeo.com/browse/NXP-8176?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Benoit Delbosc updated NXP-8176:
--------------------------------
Fix Version/s: (was: 5.5.0-HF01)
5.5.0-HF02
> PostgreSQL nx_vacuum_read_acls takes too long on large database
> ---------------------------------------------------------------
>
> Key: NXP-8176
> URL: https://jira.nuxeo.com/browse/NXP-8176
> Project: Nuxeo Enterprise Platform
> Issue Type: Improvement
> Components: Core SQL Storage
> Affects Versions: 5.5
> Environment: PostgreSQL 8.3 may be 8.4
> Reporter: Benoit Delbosc
> Fix For: 5.4.2-HF16, 5.5.0-HF02, 5.6
>
>
> The nx_vacuum_read_acls function is called at startup to remove unused acl
> read on the aclr table.
> The left join on huge table generate a poor query plan at least on PostgreSQL
> 8.3.
> {code}
> DELETE FROM aclr WHERE acl_id IN (SELECT r.acl_id FROM aclr AS r
> LEFT JOIN hierarchy_read_acl AS h ON r.acl_id=h.acl_id
> WHERE h.acl_id IS NULL);
> ---------------------------
> Nested Loop IN Join (cost=0.00..3509.08 rows=166 width=6)
> Join Filter: ((r.acl_id)::text = (aclr.acl_id)::text)
> -> Seq Scan on aclr (cost=0.00..5.66 rows=166 width=39)
> -> Merge Left Join (cost=0.00..106473.28 rows=928778 width=33)
> Merge Cond: ((r.acl_id)::text = (h.acl_id)::text)
> Filter: (h.acl_id IS NULL)
> -> Index Scan using aclr_acl_id_idx on aclr r (cost=0.00..18.74
> rows=166 width=33)
> -> Index Scan using hierarchy_read_acl_acl_id_idx on
> hierarchy_read_acl h (cost=0.00..83234.66 rows=1857557 width=33)
> {code}
> This should be rewriten to something like this:
> {code}
> EXPLAIN ANALYZE SELECT acl_id FROM aclr WHERE NOT EXISTS (SELECT 1 FROM
> hierarchy_read_acl h WHERE h.acl_id = aclr.acl_id LIMIT 1);
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on aclr (cost=0.00..23.84 rows=83 width=33) (actual
> time=3.535..3.535 rows=0 loops=1)
> Filter: (NOT (subplan))
> SubPlan
> -> Limit (cost=0.00..0.11 rows=1 width=0) (actual time=0.020..0.020
> rows=1 loops=167)
> -> Index Scan using hierarchy_read_acl_acl_id_idx on
> hierarchy_read_acl h (cost=0.00..2787.52 rows=25446 width=0) (actual
> time=0.019..0.019 rows=1 loops=167)
> Index Cond: ((acl_id)::text = ($0)::text)
> Total runtime: 3.561 ms
> {code}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
