Forum OpenACS Development: Re: acs_object__new slowness...
Jeff ... in my installation on PG 7.3 this query has a much nicer plan (no "materialize", which is a killer) and doesn't seem to run particularly slow with 8000 users. I'm going to try adding more and more users but have a few questions ...
1. What version of PG are you using?
2. You did "analyze" and restart? "analyze" has no affect on functions compiled into running backends. You have to force new backends into existence each of which will then recompile the function using the "analyze" data. The easiest way to do this is to stop and restart AOLserver (you may already know this, of course)
3. How many groups and relsegs do you have? It doesn't look like many ... what groups do the users belong to (or how many users are in your various groups)?
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)
