BUG #13869: Right Join query that never ends
От | zx-master@bigmir.net |
---|---|
Тема | BUG #13869: Right Join query that never ends |
Дата | |
Msg-id | 20160115151648.2968.21248@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #13869: Right Join query that never ends
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: bloodjazman@gmail.comДата:
Сообщение: BUG #13867: apt.postgresql.org broken for postgresql-server-dev-9.4 and libpq-dev
Следующее
От: prtkgaur1@gmail.comДата:
Сообщение: BUG #13864: Reproducible, ERROR: could not read block 30 in file "base/16414/11914": read only 0 of 8192 bytes