Forum OpenACS Development: Re: acs_object__new slowness...
I created 500k users, but only 6 rel_segments (for main site and 2 subsites), and 2 rel_constraints (one for each subsites). My daughter contributed to the experiment by turning my computer off so yeah, everything has been restarted after a vacuumdb -f -z.
I have a second db (on the same server) with 100k users, 20 rel_segs, and 3 rel_constraints and the query takes .5ms (rather than the 1-2 seconds with the dataset which is 5x larger). Same
I dropped the group_elem_idx_container_idx and on the 500k user db the query dropped to under 1ms and had this plan:
explain analyze select constraint_id, constraint_name from rel_constraints_violated_one where rel_id = 20938;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=26.42..34855.94 rows=1 width=262) (actual time=0.07..0.07 rows=0 loops=1)
Join Filter: (("inner".segment_id = "outer".required_rel_segment) AND ("inner".element_id = "outer".container_id))
Filter: ("inner".element_id IS NULL)
-> Merge Join (cost=3.05..10.36 rows=1 width=159) (actual time=0.07..0.07 rows=0 loops=1)
Merge Cond: ("outer".group_id = "inner".group_id)
Join Filter: ("outer".object_type = "inner".rel_type)
-> Nested Loop (cost=0.00..17.74 rows=5 width=133) (actual time=0.07..0.07 rows=0 loops=1)
Join Filter: (("inner".tree_sortkey >= "outer".tree_sortkey) AND ("inner".tree_sortkey <= tree_right("outer".tree_sortkey)))
-> Nested Loop (cost=0.00..14.53 rows=1 width=106) (actual time=0.07..0.07 rows=0 loops=1)
Join Filter: ("inner".object_type = "outer".rel_type)
-> Nested Loop (cost=0.00..11.54 rows=1 width=79) (actual time=0.07..0.07 rows=0 loops=1)
Join Filter: ("inner".rel_segment = "outer".segment_id)
-> Index Scan using rel_segments_grp_rel_type_uq on rel_segments rs (cost=0.00..5.32 rows=6 width=23) (actual time=0.02..0.03 rows=6 loops=1)
-> Seq Scan on rel_constraints rel (cost=0.00..1.02 rows=1 width=56) (actual time=0.00..0.00 rows=0 loops=6)
Filter: (rel_side = 'one'::bpchar)
-> Seq Scan on acs_object_types o2 (cost=0.00..2.44 rows=44 width=27) (never executed)
-> Seq Scan on acs_object_types o1 (cost=0.00..2.44 rows=44 width=27) (never executed)
-> Sort (cost=3.05..3.06 rows=3 width=26) (never executed)
Sort Key: public.group_element_index.group_id
-> Index Scan using group_elem_idx_rel_id_idx on group_element_index (cost=0.00..3.04 rows=3 width=26) (never executed)
Index Cond: (rel_id = 20938)
-> Materialize (cost=33280.71..33280.71 rows=104324 width=103) (never executed)
-> Hash Join (cost=23.36..33280.71 rows=104324 width=103) (never executed)
Hash Cond: ("outer".rel_type = "inner".object_type)
Join Filter: ("outer".group_id = "inner".group_id)
-> Seq Scan on group_element_index (cost=0.00..19349.74 rows=927174 width=26) (never executed)
-> Hash (cost=23.29..23.29 rows=29 width=77) (never executed)
-> Nested Loop (cost=1.07..23.29 rows=29 width=77) (never executed)
Join Filter: (("inner".tree_sortkey >= "outer".tree_sortkey) AND ("inner".tree_sortkey <= tree_right("outer".tree_sortkey)))
-> Hash Join (cost=1.07..4.03 rows=6 width=50) (never executed)
Hash Cond: ("outer".object_type = "inner".rel_type)
-> Seq Scan on acs_object_types o2 (cost=0.00..2.44 rows=44 width=27) (never executed)
-> Hash (cost=1.06..1.06 rows=6 width=23) (never executed)
-> Seq Scan on rel_segments rs (cost=0.00..1.06 rows=6 width=23) (never executed)
-> Seq Scan on acs_object_types o1 (cost=0.00..2.44 rows=44 width=27) (never executed)
Total runtime: 0.29 msec
(36 rows)
