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
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
