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

On Fri, Jan 15, 2016 at 8:16 AM, <zx-master@bigmir.net> wrote:

> 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, b=
ut
> 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) A=
S
> 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 =3D ko_key.id)
>         INNER JOIN ka_object ko_type ON (k.type_id =3D ko_type.id)
>         INNER JOIN key_type kt ON (k.type_id =3D kt.id)
>         LEFT OUTER JOIN keytype_uidtype_reference uidtype ON
> uidtype.keytype_id=3Dkt.id
>         INNER JOIN ka_object ko_c ON (ko_key.parent_id =3D ko_c.id)
>         INNER JOIN container keys_c ON (keys_c.id =3D ko_c.id and
> store_type=3D'Key')
>         INNER JOIN ka_object ko_cl ON (ko_c.parent_id =3D ko_cl.id)
>         INNER JOIN client client ON (ko_cl.id =3D client.id)
>         INNER JOIN ka_object ko_clc ON (ko_cl.parent_id =3D ko_clc.id)
>         INNER JOIN base_client bc ON (client.id =3D bc.id)
>         INNER JOIN ka_object ko_r ON (ko_clc.parent_id =3D ko_r.id)
>         INNER JOIN ka_user ku ON (bc.id =3D ku.id)
>         INNER JOIN ka_user kus ON (ko_r.id =3D 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 =
=3D
> kp.id)
>                         WHERE kp.name =3D 'server_license_details' AND
> kp.value =3D 'billing'
>         ) AS sld ON (sld.keytype_id =3D ko_type.id)
>         LEFT OUTER JOIN key_value exp_date ON (k.id =3D exp_date.key_id A=
ND
> exp_date.key_value_key_id=3D55 )
>
>         WHERE
>             ko_key.system=3D'f'
>             AND upper(bc.company_name) not like '%TEST%'
>             AND k.terminated=3D'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 =3D res.id)
>                 WHERE resellers.parent_id=3D33
>                     AND type =3D 'com.Reseller'
>                     AND system=3D'f'
>                     AND upper(resellers.name) NOT LIKE '%TEST%'
> ) AS all_res ON all_res.res_id =3D info.reseller_id
>
> GROUP BY info.reseller_id, reseller_nm, all_res.email, all_res.res_id


=E2=80=8BSo, typically the first thing to do is run "EXPLAIN" (or EXPLAIN A=
NALYZE -
though if the query never finishes that won't help).  Since you haven't
provided a self-contained test case you are going to need to do that for us=
.

David J.
=E2=80=8B