gistfile1.txt

EXPLAIN ANALYSE
SELECT COUNT(*)
FROM aps_application_instances ai
    JOIN aps_service_instances si ON (ai.app_instance_id = si.app_instance_id  AND si.is_basic = 'y' )
    LEFT JOIN (
        aps_packages_upgrade_relations pur
        JOIN aps_packages pu ON (pur.app_id = pu.app_id AND pur.tgt_ver = pu.app_ver AND pu.disabled = 'n' )
    ) ON (ai.app_id = pur.app_id AND ai.app_ver = pur.src_ver)
    LEFT JOIN aps_service_instance_operation_requests sior ON (sior.service_instance_id = si.service_instance_id AND sior.operation = 'u')
    JOIN aps_external_application_instances hai ON (ai.app_instance_id = hai.app_instance_id)
    LEFT JOIN saas_application_instances saas_ai ON (ai.app_instance_id = saas_ai.app_instance_id)
    LEFT JOIN domains pd ON saas_ai.domain_id = pd.domain_id
    LEFT JOIN (
        SELECT app_instance_id, array_to_string(array_agg(name), ', ') AS names
        FROM domains
            JOIN saas_domain_bindings sdb ON (domains.domain_id = sdb.domain_id)
        GROUP BY app_instance_id
    ) AS d ON (ai.app_instance_id = d.app_instance_id)
    LEFT JOIN (
        SELECT app_instance_id, COUNT(hsu_id) AS users_number
        FROM saas_users
        GROUP BY app_instance_id
    ) AS un ON (ai.app_instance_id = un.app_instance_id)
    LEFT JOIN (
        subs_resources sr
        JOIN subscriptions sub ON (sr.sub_id = sub.sub_id)
        JOIN resource_types rt ON (sr.rt_id = rt.rt_id)
    ) ON (sr.rt_instance_id = ai.resource_id)
    ,accounts acc
    ,hosts h
WHERE h.host_id = hai.host_id
    AND ai.app_id = 322
    AND acc.account_id = ai.owner_id
    AND (
        pu.version_order IS NULL
        OR pu.version_order = (
            SELECT MIN(version_order)
            FROM aps_packages
            WHERE app_id = pu.app_id
                 AND (disabled = 'n' OR ai.owner_id = 1))
    )
    AND (COALESCE(pu.app_ver, '') != '') AND (COALESCE(sior.requested, 'n') = 'n')
;