-
Type:
Improvement
-
Status: Resolved
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.5
-
Fix Version/s: 5.4.2-HF16, 5.5.0-HF02, 5.6-RC1, 5.6
-
Component/s: Core VCS
-
Environment:PostgreSQL 8.3 may be 8.4
-
Tags:
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.
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)
This should be rewriten to something like this:
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
