c2c-oerpscenario team mailing list archive

["Olivier Dony \(OpenERP\)" <591115@xxxxxxxxxxxxxxxxxx>]

Hello Ferdinand,

As explained by Panos, regardless of the indices you set on a table, the query planner of postgres may still correctly decide to use a Seq Scan of small tables, according to the database statistics.
The indices on ir.rule seem appropriate at the moment, but we are still looking for ways to improve ir.rule performances (rules are also cache BTW) as it is one of the most frequently accessed table indeed.

Note also that the structure of ir.rules has been simplified and we have
removed the ir.rule.group object completely in v6.

** Changed in: openobject-server
       Status: New => Invalid

-- 
Performance issue ir_rule
https://bugs.launchpad.net/bugs/591115
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.

Status in OpenObject Server: Invalid

Bug description:
this statement was executed ~255.000 times during ONE day 1-2 persons working
statistics prepared with  epqa_1.2.tgz
IMHO "Seq Scan" shouldn't appear in such an "important" query

Rank 1 (Queries Took Most Time)
Total time 57.046
Times 254567
executed Query
  SELECT r.id FROM ir_rule r join (ir_rule_group g join ir_model m on (g.model_id=m.id)) on (g.id=r.rule_group) WHERE m.model=e{} AND (g.id in (SELECT rule_group_id FROM group_rule_group_rel g_rel join res_groups_users_rel u_rel on (g_rel.group_id=u_rel.gid) WHERE u_rel.uid={}) or g.global)

SELECT r.id FROM
                    ir_rule r
                        JOIN (ir_rule_group g
                            JOIN ir_model m ON (g.model_id = m.id))
                            ON (g.id = r.rule_group)
                        WHERE m.model = E'audittrail.rule'
                        AND (g.id IN (SELECT rule_group_id FROM group_rule_group_rel g_rel
                                    JOIN res_groups_users_rel u_rel ON (g_rel.group_id = u_rel.gid)
                                    WHERE u_rel.uid = 7) OR g.global)

                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=68.10..78.47 rows=1 width=4)
   Join Filter: (g.id = r.rule_group)
   ->  Nested Loop  (cost=68.10..77.42 rows=1 width=4)
         Join Filter: (g.model_id = m.id)
         ->  Index Scan using ir_model_model_index on ir_model m  (cost=0.00..8.27 rows=1 width=4)
               Index Cond: ((model)::text = 'audittrail.rule'::text)
         ->  Seq Scan on ir_rule_group g  (cost=68.10..69.13 rows=2 width=8)
               Filter: ((hashed subplan) OR g.global)
               SubPlan
                 ->  Nested Loop  (cost=0.00..67.25 rows=342 width=4)
                       ->  Seq Scan on res_groups_users_rel u_rel  (cost=0.00..4.69 rows=32 width=4)
                             Filter: (uid = 7)
                       ->  Index Scan using group_rule_group_rel_group_id_index on group_rule_group_rel g_rel  (cost=0.00..1.82 rows=11 width=8)
                             Index Cond: (g_rel.group_id = u_rel.gid)
   ->  Seq Scan on ir_rule r  (cost=0.00..1.02 rows=2 width=8)
(15 rows)