Forum OpenACS Development: Re: acs_object__new slowness...

Collapse
Posted by Jeff Davis on
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)