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

From Master ZX
Subject Re[2]: [BUGS] BUG #13869: Right Join query that never ends
Date
Msg-id E1aKQKO-0004go-Qh@bst01.sputnikmedia.net
Whole thread Raw
In response to Re: BUG #13869: Right Join query that never ends  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Re[2]: [BUGS] BUG #13869: Right Join query that never ends
List pgsql-bugs
Greetings.

I can do more to investigate this behavior only if postgresql will consider my claim as bug that has to be fixed in
firstorder. 
Ideally  I should give my DB schema and dump or VM wich reproduces this bug, but I can not give away our internal data.
Allother options I can not describe at public mailing list. 

> So, typically the first thing to do is run "EXPLAIN"
Explain Analyze do not return any result as select query.
So this is just an explain output

HashAggregate  (cost=962.11..962.12 rows=1 width=70)
   Group Key: ko_r.id, resellers.name, res.email, resellers.id
   ->  Nested Loop  (cost=23.76..962.09 rows=1 width=70)
         ->  Nested Loop Left Join  (cost=23.49..957.79 rows=1 width=49)
               ->  Bitmap Heap Scan on ka_object resellers  (cost=3.59..212.91 rows=1 width=25)
                     Recheck Cond: (parent_id = 4)
                     Filter: (((type)::text = 'com.plesk.ka.sys.Reseller'::text) AND (system = 'f'::bpchar) AND
(upper((name)::text)!~~ '%TEST%'::text)) 
                     ->  Bitmap Index Scan on ka_object_parent_id_idx  (cost=0.00..3.59 rows=174 width=0)
                           Index Cond: (parent_id = 4)
               ->  Nested Loop Left Join  (cost=19.90..744.87 rows=1 width=24)
                     ->  Nested Loop Left Join  (cost=19.61..744.46 rows=1 width=32)
                           ->  Nested Loop  (cost=19.32..743.97 rows=1 width=40)
                                 ->  Nested Loop  (cost=19.05..743.67 rows=1 width=72)
                                       ->  Nested Loop  (cost=18.77..743.36 rows=1 width=48)
                                             Join Filter: (ko_r.id = ko_clc.parent_id)
                                             ->  Nested Loop  (cost=18.49..742.99 rows=1 width=64)
                                                   ->  Nested Loop  (cost=18.21..742.69 rows=1 width=56)
                                                         ->  Nested Loop  (cost=0.56..4.61 rows=1 width=16)
                                                               ->  Index Only Scan using ka_object_pkey on ka_object
ko_r (cost=0.29..4.30 rows=1 width=8) 
                                                                     Index Cond: (id = resellers.id)
                                                               ->  Index Only Scan using ka_user_pkey on ka_user kus
(cost=0.28..0.29rows=1 width=8) 
                                                                     Index Cond: (id = ko_r.id)
                                                         ->  Nested Loop  (cost=17.65..737.81 rows=27 width=40)
                                                               Join Filter: (ko_key.parent_id = keys_c.id)
                                                               ->  Merge Join  (cost=17.37..711.31 rows=85 width=56)
                                                                     Merge Cond: (ko_type.id = k.type_id)
                                                                     Join Filter: (ko_key.id = k.id)
                                                                     ->  Nested Loop  (cost=0.99..1543.85 rows=768
width=64)
                                                                           ->  Nested Loop  (cost=0.42..40.77 rows=8
width=16)
                                                                                 ->  Index Only Scan using
key_type_pkeyon key_type kt  (cost=0.13..6.25 rows 
=8 width=8)
                                                                                 ->  Index Only Scan using
ka_object_pkeyon ka_object ko_type  (cost=0.29..4. 
30 rows=1 width=8)
                                                                                       Index Cond: (id = kt.id)
                                                                           ->  Materialize  (cost=0.57..1493.72 rows=96
width=48)
                                                                                 ->  Nested Loop  (cost=0.57..1493.24
rows=96width=48) 
                                                                                       ->  Nested Loop
(cost=0.29..1458.21rows=96 width=32) 
                                                                                             ->  Seq Scan on ka_object
ko_key (cost=0.00..1149.97 rows=96 wid 
th=16)
                                                                                                   Filter: ((system =
'f'::bpchar)AND ((create_date)::date >= 
 date_trunc('month'::text, (now() + '-1 mons'::interval))) AND ((create_date)::date < date_trunc('month'::text, (now()
+'00:00:00'::interval)))) 
                                                                                             ->  Index Scan using
ka_object_pkeyon ka_object ko_c  (cost=0.29 
..3.20 rows=1 width=16)
                                                                                                   Index Cond: (id =
ko_key.parent_id)
                                                                                       ->  Index Scan using
ka_object_pkeyon ka_object ko_cl  (cost=0.29..0.3 
5 rows=1 width=16)
                                                                                             Index Cond: (id =
ko_c.parent_id)
                                                                     ->  Index Scan using key_type_id_idx on key k
(cost=0.29..602.90rows=17049 width=16) 
                                                               ->  Index Scan using container_pkey on container keys_c
(cost=0.28..0.30rows=1 width=8) 
                                                                     Index Cond: (id = ko_c.id)
                                                                     Filter: ((store_type)::text = 'Key'::text)
                                                   ->  Index Only Scan using client_pkey on client  (cost=0.27..0.29
rows=1width=8) 
                                                         Index Cond: (id = ko_c.parent_id)
                                             ->  Index Scan using ka_object_pkey on ka_object ko_clc  (cost=0.29..0.35
rows=1width=16) 
                                                   Index Cond: (id = ko_cl.parent_id)
                                       ->  Index Scan using base_client_pkey on base_client bc  (cost=0.28..0.30 rows=1
width=24)
                                             Index Cond: (id = ko_c.parent_id)
                                             Filter: (upper((company_name)::text) !~~ '%TEST%'::text)
                                 ->  Index Only Scan using ka_user_pkey on ka_user ku  (cost=0.28..0.29 rows=1 width=8)
                                       Index Cond: (id = ko_c.parent_id)
                           ->  Nested Loop  (cost=0.28..0.48 rows=1 width=8)
                                 ->  Index Only Scan using key_property_reference_keytype_id_property_id_unique_idx on
key_property_referencekpr  (cost=0.14. 
.0.16 rows=1 width=16)
                                       Index Cond: (keytype_id = ko_type.id)
                                 ->  Index Scan using key_property_pkey on key_property kp  (cost=0.14..0.31 rows=1
width=8)
                                       Index Cond: (id = kpr.property_id)
                                       Filter: (((name)::text = 'server_license_details'::text) AND ((value)::text =
'billing'::text))
                     ->  Index Only Scan using key_value_key_id_and_kvk_id_idx on key_value exp_date  (cost=0.29..0.39
rows=1width=8) 
                           Index Cond: ((key_id = k.id) AND (key_value_key_id = 63))
         ->  Index Scan using ka_user_pkey on ka_user res  (cost=0.28..4.29 rows=1 width=29)
               Index Cond: (id = resellers.id)



pgsql-bugs by date:

Previous
From: praveenprakash.nic@gmail.com
Date:
Subject: BUG #13873: Pg_upgrade problem from 9.4 to 9.5
Next
From: Michael Paquier
Date:
Subject: Re: about CREATE EXTENSION error