Forum OpenACS Development: Re: OpenACS Permissions System - Performance Improvement Work

Don,

I am now tryiung different ways to put permissions on projects at the query level. I would value your opinion as to which of these is the preferred method (or any other you can devise!).

A) QUERY WITH PERMISSIONS CALL IN SELECT STATEMENT

Reqires tcl code to control display of data. I tried adding:

 and permission='t'
to the where clause but postgres will not allow me to refer to the column using the affiliation name. Is there a way to do this (i.e. with column number as with an aggregate function).

explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s
        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id;

tradeadev#                                                            QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4010.03..4129.91 rows=2 width=65) (actual time=37.805..37.805 rows=0 loops=1)
   ->  Hash Join  (cost=4010.03..4016.69 rows=2 width=73) (actual time=37.799..37.799 rows=0 loops=1)
         Hash Cond: ("outer".status_id = "inner".status_id)
         ->  Hash Left Join  (cost=4009.01..4015.63 rows=2 width=77) (actual time=37.680..37.680 rows=0 loops=1)
               Hash Cond: ("outer".item_id = "inner".object_id)
               ->  Nested Loop Left Join  (cost=4008.98..4015.59 rows=2 width=81) (actual time=37.621..37.621 rows=0 loops=1)
                     ->  Merge Left Join  (cost=4008.98..4009.22 rows=2 width=85) (actual time=37.618..37.618 rows=0 loops=1)
                           Merge Cond: ("outer".item_id = "inner".project_id)
                           ->  Sort  (cost=4006.38..4006.39 rows=2 width=85) (actual time=37.613..37.613 rows=0 loops=1)
                                 Sort Key: cr.item_id
                                 ->  Nested Loop  (cost=0.00..4006.37 rows=2 width=85) (actual time=37.583..37.583 rows=0 loops=1)
                                       ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
                                       ->  Nested Loop  (cost=0.00..4005.34 rows=2 width=85) (actual time=37.550..37.550 rows=0 loops=1)
                                             ->  Nested Loop  (cost=0.00..4001.84 rows=1 width=81) (actual time=37.548..37.548 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=0.00..3996.09 rows=1 width=61) (actual time=37.545..37.545 rows=0 loops=1)
                                                         ->  Seq Scan on pm_projects  (cost=0.00..3992.51 rows=1 width=26) (actual time=37.543..37.543 rows=0 loops=1)
                                                               Filter: ((subplan) = true)
                                                               SubPlan
                                                                 ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.490..0.491 rows=1 loops=75)
                                                                       InitPlan
                                                                         ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.480..0.480 rows=0 loops=75)
                                                                               Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                               ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.194 rows=1 loops=75)
                                                                                     Filter: ((descendant)::text = 'admin'::text)
                                                                               ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
                                                                                     ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.263..0.263 rows=0 loops=75)
                                                                                           Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                           ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.049..0.242 rows=8 loops=75)
                                                                                                 ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.021..0.048 rows=6 loops=75)
                                                                                                       Index Cond: (object_id = $0)
                                                                                                 ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                       Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                           ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.083..0.083 rows=0 loops=1)
                                                                                                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.057..0.063 rows=1 loops=1)
                                                                                                       Index Cond: (member_id = -1)
                                                         ->  Index Scan using cr_revisions_pk on cr_revisions cr  (cost=0.00..3.57 rows=1 width=35) (never executed)
                                                               Index Cond: ("outer".project_id = cr.revision_id)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..3.49 rows=1 width=8) (never executed)
                                                   Index Cond: ("outer".item_id = i.item_id)
                           ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                 Sort Key: pa.project_id
                                 ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..3.17 rows=1 width=4) (never executed)
                           Index Cond: ("outer".customer_id = o.organization_id)
               ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                           Join Filter: ("inner".category_id = "outer".category_id)
                           ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
                                 ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                       Filter: (deprecated_p = 'f'::bpchar)
                           ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
         ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.028..0.036 rows=2 loops=1)
   ->  Index Scan using cr_items_by_live_revision on cr_items i  (cost=0.00..3.40 rows=1 width=4) (never executed)
         Index Cond: (i.live_revision = "outer".revision_id)
   SubPlan
     ->  Result  (cost=53.18..53.19 rows=1 width=0) (never executed)
           InitPlan
             ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (never executed)
                   Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                   ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (never executed)
                         Filter: ((descendant)::text = 'admin'::text)
                   ->  Hash  (cost=51.11..51.11 rows=1 width=9) (never executed)
                         ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (never executed)
                               Hash Cond: ("outer".grantee_id = "inner".party_id)
                               ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (never executed)
                                     ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (never executed)
                                           Index Cond: (object_id = $0)
                                     ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (never executed)
                                           Index Cond: ("outer".ancestor_id = p.object_id)
                               ->  Hash  (cost=3.45..3.45 rows=2 width=4) (never executed)
                                     ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (never executed)
                                           Index Cond: (member_id = -1)
 Total runtime: 39.795 ms
(78 rows)

B) WRAP ABOVE IN AND OUTER SELECT TO EXTRACT ONLY REQUIRED ROWS

explain analyse SELECT * from (SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s

        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id) as project_list where permission='t';

                                                                    QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4010.03..4129.91 rows=2 width=65) (actual time=45.897..45.897 rows=0 loops=1)
   ->  Hash Join  (cost=4010.03..4016.69 rows=2 width=73) (actual time=45.891..45.891 rows=0 loops=1)
         Hash Cond: ("outer".status_id = "inner".status_id)
         ->  Hash Left Join  (cost=4009.01..4015.63 rows=2 width=77) (actual time=45.768..45.768 rows=0 loops=1)
               Hash Cond: ("outer".item_id = "inner".object_id)
               ->  Nested Loop Left Join  (cost=4008.98..4015.59 rows=2 width=81) (actual time=45.710..45.710 rows=0 loops=1)
                     ->  Merge Left Join  (cost=4008.98..4009.22 rows=2 width=85) (actual time=45.708..45.708 rows=0 loops=1)
                           Merge Cond: ("outer".item_id = "inner".project_id)
                           ->  Sort  (cost=4006.38..4006.39 rows=2 width=85) (actual time=45.703..45.703 rows=0 loops=1)
                                 Sort Key: cr.item_id
                                 ->  Nested Loop  (cost=0.00..4006.37 rows=2 width=85) (actual time=45.669..45.669 rows=0 loops=1)
                                       ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
                                       ->  Nested Loop  (cost=0.00..4005.34 rows=2 width=85) (actual time=45.637..45.637 rows=0 loops=1)
                                             ->  Nested Loop  (cost=0.00..4001.84 rows=1 width=81) (actual time=45.634..45.634 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=0.00..3996.09 rows=1 width=61) (actual time=45.632..45.632 rows=0 loops=1)
                                                         ->  Seq Scan on pm_projects  (cost=0.00..3992.51 rows=1 width=26) (actual time=45.629..45.629 rows=0 loops=1)
                                                               Filter: ((subplan) = true)
                                                               SubPlan
                                                                 ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.495..0.496 rows=1 loops=75)
                                                                       InitPlan
                                                                         ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.484..0.484 rows=0 loops=75)
                                                                               Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                               ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.123..0.195 rows=1 loops=75)
                                                                                     Filter: ((descendant)::text = 'admin'::text)
                                                                               ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.269..0.269 rows=0 loops=75)
                                                                                     ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
                                                                                           Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                           ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.244 rows=8 loops=75)
                                                                                                 ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
                                                                                                       Index Cond: (object_id = $0)
                                                                                                 ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                       Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                           ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.085..0.085 rows=0 loops=1)
                                                                                                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.060..0.067 rows=1 loops=1)
                                                                                                       Index Cond: (member_id = -1)
                                                         ->  Index Scan using cr_revisions_pk on cr_revisions cr  (cost=0.00..3.57 rows=1 width=35) (never executed)
                                                               Index Cond: ("outer".project_id = cr.revision_id)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..3.49 rows=1 width=8) (never executed)
                                                   Index Cond: ("outer".item_id = i.item_id)
                           ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                 Sort Key: pa.project_id
                                 ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..3.17 rows=1 width=4) (never executed)
                           Index Cond: ("outer".customer_id = o.organization_id)
               ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                           Join Filter: ("inner".category_id = "outer".category_id)
                           ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                 ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                       Filter: (deprecated_p = 'f'::bpchar)
                           ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
         ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.061..0.061 rows=0 loops=1)
               ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.030..0.039 rows=2 loops=1)
   ->  Index Scan using cr_items_by_live_revision on cr_items i  (cost=0.00..3.40 rows=1 width=4) (never executed)
         Index Cond: (i.live_revision = "outer".revision_id)
   SubPlan
     ->  Result  (cost=53.18..53.19 rows=1 width=0) (never executed)
           InitPlan
             ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (never executed)
                   Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                   ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (never executed)
                         Filter: ((descendant)::text = 'admin'::text)
                   ->  Hash  (cost=51.11..51.11 rows=1 width=9) (never executed)
                         ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (never executed)
                               Hash Cond: ("outer".grantee_id = "inner".party_id)
                               ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (never executed)
                                     ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (never executed)
                                           Index Cond: (object_id = $0)
                                     ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (never executed)
                                           Index Cond: ("outer".ancestor_id = p.object_id)
                               ->  Hash  (cost=3.45..3.45 rows=2 width=4) (never executed)
                                     ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (never executed)
                                           Index Cond: (member_id = -1)
 Total runtime: 47.817 ms
(78 rows)

C)

tradeadev=# explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code 
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s
        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id and (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin'));


                                                                 QUERY PLAN                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4289.71..4304.48 rows=32 width=65) (actual time=41.532..41.532 rows=0 loops=1)
   Hash Cond: ("outer".status_id = "inner".status_id)
   ->  Hash Join  (cost=4288.68..4302.97 rows=32 width=69) (actual time=41.410..41.410 rows=0 loops=1)
         Hash Cond: ("outer".live_revision = "inner".revision_id)
         ->  Seq Scan on cr_items i  (cost=0.00..11.98 rows=398 width=4) (actual time=0.006..0.856 rows=398 loops=1)
         ->  Hash  (cost=4288.58..4288.58 rows=39 width=77) (actual time=39.722..39.722 rows=0 loops=1)
               ->  Hash Left Join  (cost=4255.16..4288.58 rows=39 width=77) (actual time=39.719..39.719 rows=0 loops=1)
                     Hash Cond: ("outer".item_id = "inner".object_id)
                     ->  Merge Right Join  (cost=4255.13..4288.35 rows=39 width=81) (actual time=39.667..39.667 rows=0 loops=1)
                           Merge Cond: ("outer".organization_id = "inner".customer_id)
                           ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..29.92 rows=1201 width=4) (actual time=0.033..0.033 rows=1 loops=1)
                           ->  Sort  (cost=4255.13..4255.23 rows=39 width=85) (actual time=39.630..39.630 rows=0 loops=1)
                                 Sort Key: pm_projects.customer_id
                                 ->  Merge Left Join  (cost=4027.44..4254.10 rows=39 width=85) (actual time=39.588..39.588 rows=0 loops=1)
                                       Merge Cond: ("outer".item_id = "inner".project_id)
                                       ->  Nested Loop  (cost=4024.84..4270.01 rows=39 width=85) (actual time=39.585..39.585 rows=0 loops=1)
                                             ->  Nested Loop  (cost=4023.83..4268.22 rows=39 width=85) (actual time=39.583..39.583 rows=0 loops=1)
                                                   ->  Merge Join  (cost=4023.83..4043.93 rows=39 width=65) (actual time=39.579..39.579 rows=0 loops=1)
                                                         Merge Cond: ("outer".item_id = "inner".item_id)
                                                         ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..18.52 rows=398 width=8) (actual time=0.016..0.016 rows=1 loops=1)
                                                         ->  Sort  (cost=4023.83..4023.93 rows=38 width=61) (actual time=39.559..39.559 rows=0 loops=1)
                                                               Sort Key: cr.item_id
                                                               ->  Hash Join  (cost=3992.42..4022.83 rows=38 width=61) (actual time=39.510..39.510 rows=0 loops=1)
                                                                     Hash Cond: ("outer".revision_id = "inner".project_id)
                                                                     ->  Seq Scan on cr_revisions cr  (cost=0.00..27.69 rows=469 width=35) (actual time=0.007..1.031 rows=469 loops=1)
                                                                     ->  Hash  (cost=3992.32..3992.32 rows=38 width=26) (actual time=37.637..37.637 rows=0 loops=1)
                                                                           ->  Seq Scan on pm_projects  (cost=0.00..3992.32 rows=38 width=26) (actual time=37.634..37.634 rows=0 loops=1)
                                                                                 Filter: (subplan)
                                                                                 SubPlan
                                                                                   ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.492..0.494 rows=1 loops=75)
                                                                                         InitPlan
                                                                                           ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.482..0.482 rows=0 loops=75)
                                                                                                 Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                                                 ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.195 rows=1 loops=75)
                                                                                                       Filter: ((descendant)::text = 'admin'::text)
                                                                                                 ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.268..0.268 rows=0 loops=75)
                                                                                                       ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.264..0.264 rows=0 loops=75)
                                                                                                             Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                                             ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.243 rows=8 loops=75)
                                                                                                                   ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
                                                                                                                         Index Cond: (object_id = $0)
                                                                                                                   ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                                         Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                                             ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.078..0.078 rows=0 loops=1)
                                                                                                                   ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.051..0.057 rows=1 loops=1)
                                                                                                                         Index Cond: (member_id = -1)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Materialize  (cost=1.01..1.02 rows=1 width=0) (never executed)
                                                   ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (never executed)
                                       ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                             Sort Key: pa.project_id
                                             ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                       Join Filter: ("inner".category_id = "outer".category_id)
                                       ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                       ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                             Filter: (deprecated_p = 'f'::bpchar)
                                 ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
   ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.057..0.057 rows=0 loops=1)
         ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.024..0.032 rows=2 loops=1)
 Total runtime: 43.013 ms
(65 rows)

tradeadev=#