Forum .LRN Q&A: Bad performance in one query

Hello, all!

I am getting very bad performance in one query (More than
12 seconds). I think of the problem is in one
sequential access (Seq Scan on apm_packages ap ).
I have seen the indexes and they appear ok.

I am using Postgres and dor 2.0.2. The query and
the explain is below.

Any pointer, please?

Best regards,
Agustin

-------------------------------------------------
explain select package_id,
            ap.package_key,
            acs_object__name(package_id) as instance_name,
            apm_package_type__num_parameters(ap.package_key)
                as parameter_count
    from apm_packages ap,
          apm_package_types
    where ap.package_key = apm_package_types.package_key
    and package_type = 'apm_service'
    and (acs_permission__permission_p
            (package_id, '2173', 'read')
        = 't'
          or acs_permission__permission_p(package_id,
          acs__magic_object_id('the_public'), 'read') = 't'
        )
    and  (ap.package_key != 'acs-subsite' or (select
      parent_id from site_nodes where object_id =
          package_id) is null)
    order by instance_name;

                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Sort  (cost=73098.88..73099.12 rows=96 width=14)
  Sort Key: acs_object__name(ap.package_id)
  ->  Hash Join  (cost=4.04..73095.72 rows=96 width=14)
        Hash Cond: (("outer".package_key)::text =
            ("inner".package_key)::text)
        ->  Seq Scan on apm_packages ap
            (cost=0.00..73089.57 rows=132 width=14)
              Filter: (((acs_permission__permission_p
              (package_id, 2173, 'read'::character varying)
                = true) OR
                (acs_permission__permission_p(package_id,
                -1, 'read'::character varying) = true))
                AND (((package_key
                )::text <> 'acs-subsite'::text) OR
                  ((subplan) IS NULL)))
              SubPlan
                ->  Index Scan using
                    site_nodes_object_id_idx on
                  site_nodes
                (cost=0.00..5.52 rows=2 width=4)
                      Index Cond: (object_id = $0)
              ->  Hash  (cost=3.91..3.91 rows=53 width=16)
              ->  Seq Scan on apm_package_types
                (cost=0.00..3.91 rows=53 width=16)
                    Filter: ((package_type)::text =
                      'apm_service'::text)

-----------------------------------

Could you please remove this clause:

and (acs_permission__permission_p (package_id, '2173', 'read') = 't' or acs_permission__permission_p(package_id, acs__magic_object_id('the_public'), 'read') = 't' )

and tell us how long it the query takes and also could you post the resulting plan?

Thanks ...