BUG #13869: Right Join query that never ends - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      13869
Logged by:          Rumato
Email address:      zx-master@bigmir.net
PostgreSQL version: 9.5.0
Operating system:   CentOS 7
Description:

Hi.
On my old installation with pg 8.4 this query executes about 3 seconds, but
on 9.5  it never ends and cpu usage rises up to 100% by postgres process.

Problem is here near RIGHT OUTER JOIN
Query works with LEFT OUTER JOIN and other inner joins

If I will create 2 views from "AS info" (about 2k rows) and "AS all_res"
(about 200 rows) tables it will not fix anything. BTW separate "AS info"
query executes less than in 3 sec, "AS all_res" less than 1 sec.

I have solved this problem by using WITH info, all_res queries. Now it works
pretty fast.

THE QUERY
SELECT
    all_res.res_id reseller_id,
    'keys' as descrip,
    count(info.company_name) as total,
    all_res.reseller_nm as reseller_name,
    all_res.email as reseller_email
FROM (SELECT
        k.id AS key_id,
        (case when uidtype.keytype_id is null then null else k.uid end) AS
key_guid,
        k.type_id as type_id,
        k.binding_ip_id AS binding_ip_id,
        ko_cl.parents AS ko_cl_parents,
        bc.id AS bc_id,
        ko_key.name AS key_number,
        ko_type.name AS keytype,
        kt.class AS keytype_class,
        ko_r.name AS reseller_name,
        ko_r.id AS reseller_id,
        bc.company_name AS company_name,
        to_char(ko_key.create_date, 'yyyy MM dd HH24 mi') AS create_date,
        k.terminated AS key_terminated,
        ku.login AS login,
        sld.value AS sld,
        exp_date.value AS expiration_date,
        kus.email
    FROM key k
        INNER JOIN ka_object ko_key ON (k.id = ko_key.id)
        INNER JOIN ka_object ko_type ON (k.type_id = ko_type.id)
        INNER JOIN key_type kt ON (k.type_id = kt.id)
        LEFT OUTER JOIN keytype_uidtype_reference uidtype ON
uidtype.keytype_id=kt.id
        INNER JOIN ka_object ko_c ON (ko_key.parent_id = ko_c.id)
        INNER JOIN container keys_c ON (keys_c.id = ko_c.id and
store_type='Key')
        INNER JOIN ka_object ko_cl ON (ko_c.parent_id = ko_cl.id)
        INNER JOIN client client ON (ko_cl.id = client.id)
        INNER JOIN ka_object ko_clc ON (ko_cl.parent_id = ko_clc.id)
        INNER JOIN base_client bc ON (client.id = bc.id)
        INNER JOIN ka_object ko_r ON (ko_clc.parent_id = ko_r.id)
        INNER JOIN ka_user ku ON (bc.id = ku.id)
        INNER JOIN ka_user kus ON (ko_r.id = kus.id)
        LEFT OUTER JOIN (SELECT kpr.keytype_id, kp.value
                        FROM key_property_reference kpr
                        INNER JOIN key_property kp ON (kpr.property_id =
kp.id)
                        WHERE kp.name = 'server_license_details' AND
kp.value = 'billing'
        ) AS sld ON (sld.keytype_id = ko_type.id)
        LEFT OUTER JOIN key_value exp_date ON (k.id = exp_date.key_id AND
exp_date.key_value_key_id=55 )

        WHERE
            ko_key.system='f'
            AND upper(bc.company_name) not like '%TEST%'
            AND k.terminated='f'

) AS info

RIGHT OUTER JOIN (SELECT resellers.id as res_id, res.email as email,
resellers.name as reseller_nm
                FROM ka_object resellers
                JOIN ka_user res ON (resellers.id = res.id)
                WHERE resellers.parent_id=33
                    AND type = 'com.Reseller'
                    AND system='f'
                    AND upper(resellers.name) NOT LIKE '%TEST%'
) AS all_res ON all_res.res_id = info.reseller_id

GROUP BY info.reseller_id, reseller_nm, all_res.email, all_res.res_id