That plan looks like it might have been cropped in places, and the formatting is making it tricky to help.
Could you try again, pasting the plan into https://explain.depesz.com/ to make it easier to review? On Fri, Apr 3, 2020 at 5:18 PM dangal <danielito.ga...@gmail.com> wrote: > Justin thank you very much for your answer, as you can also see the number > of > rows differs a lot > I attach the complete explain, do not attach it because it is large > > "HashAggregate (cost=12640757.46..12713163.46 rows=385 width=720) (actual > time=1971962.023..1971962.155 rows=306 loops=1)" > " Output: bi.bus_ent_inst_name_num, bi.att_value_num_7, bi.att_value_10, > ((SubPlan 1)), ((SubPlan 2)), a2.ent_inst_att_str_value, ba.att_value_1, > depto2.att_value_1, loc2.att_value_1, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis (...)" > " Group Key: bi.bus_ent_inst_name_num, bi.att_value_num_7, > bi.att_value_10, > (SubPlan 1), (SubPlan 2), a2.ent_inst_att_str_value, ba.att_value_1, > depto2.att_value_1, loc2.att_value_1, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis. (...)" > " Buffers: shared hit=5817744 read=1034292 dirtied=790, local hit=2" > " -> Nested Loop (cost=11114347.52..12640740.13 rows=385 width=720) > (actual time=1906401.083..1971959.176 rows=306 loops=1)" > " Output: bi.bus_ent_inst_name_num, bi.att_value_num_7, > bi.att_value_10, (SubPlan 1), (SubPlan 2), a2.ent_inst_att_str_value, > ba.att_value_1, depto2.att_value_1, loc2.att_value_1, > att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, att_b > (...)" > " Buffers: shared hit=5817744 read=1034292 dirtied=790, local hit=2" > " -> Hash Join (cost=11114346.94..12228344.41 rows=1427 width=704) > (actual time=1906372.468..1964409.907 rows=306 loops=1)" > " Output: bi.bus_ent_inst_name_num, bi.att_value_num_7, > bi.att_value_10, ba.bus_ent_inst_id_auto, ba.att_value_1, > att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, > att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value, a (...)" > " Hash Cond: (ba.att_value_num_1 = > (bi.bus_ent_inst_name_num)::numeric)" > " Buffers: shared hit=5814458 read=1033324 dirtied=790, local > hit=2" > " -> Hash Right Join (cost=11114339.65..12172907.42 > rows=886647 width=158) (actual time=1906344.617..1963668.889 rows=3362294 > loops=1)" > " Output: ba.bus_ent_inst_id_auto, ba.att_value_1, > ba.att_value_num_1, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_str_value, > att_pad.ent_inst_att_str_value, att_manz.ent_inst_att_str_value, att_a > (...)" > " Hash Cond: ((att_barr.env_id = ba.env_id) AND > (att_barr.bus_ent_inst_id = ba.bus_ent_inst_id_auto))" > " Buffers: shared hit=5814458 read=1033324 dirtied=790" > " -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_barr (cost=0.83..1024093.06 rows=4508264 > width=24) (actual time=10.435..52888.091 rows=4244011 loops=1)" > " Output: att_barr.att_id, > att_barr.ent_inst_att_str_value, att_barr.env_id, att_barr.bus_ent_inst_id, > att_barr.reg_status" > " Index Cond: (att_barr.att_id = 1115)" > " Heap Fetches: 120577" > " Buffers: shared hit=503194 read=31197 dirtied=5" > " -> Hash (cost=11101039.12..11101039.12 rows=886647 > width=146) (actual time=1906329.888..1906329.888 rows=3362294 loops=1)" > " Output: ba.bus_ent_inst_id_auto, ba.att_value_1, > ba.env_id, ba.att_value_num_1, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_str_value, > att_pad.ent_inst_att_str_value, att_manz.ent_inst_att (...)" > " Buckets: 4194304 (originally 1048576) Batches: > 1 > (originally 1) Memory Usage: 396824kB" > " Buffers: shared hit=5311264 read=1002127 > dirtied=785" > " -> Hash Right Join > (cost=10328938.09..11101039.12 rows=886647 width=146) (actual > time=1867557.718..1904218.946 rows=3362294 loops=1)" > " Output: ba.bus_ent_inst_id_auto, > ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, > att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value, > att_manz.ent_in (...)" > " Hash Cond: ((att_apt.env_id = ba.env_id) > AND (att_apt.bus_ent_inst_id = ba.bus_ent_inst_id_auto))" > " Buffers: shared hit=5311264 read=1002127 > dirtied=785" > " -> Index Only Scan using > ix_bus_ent_inst_attr_03 on public.bus_ent_inst_attribute att_apt > (cost=0.83..746958.06 rows=3287982 width=24) (actual time=0.091..32788.731 > rows=3491599 loops=1)" > " Output: att_apt.att_id, > att_apt.ent_inst_att_str_value, att_apt.env_id, att_apt.bus_ent_inst_id, > att_apt.reg_status" > " Index Cond: (att_apt.att_id = 1113)" > " Heap Fetches: 88910" > " Buffers: shared hit=178090 > read=25341 > dirtied=5" > " -> Hash (cost=10315637.55..10315637.55 > rows=886647 width=130) (actual time=1867553.445..1867553.445 rows=3362294 > loops=1)" > " Output: ba.bus_ent_inst_id_auto, > ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, > att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value, att_manz. > (...)" > " Buckets: 4194304 (originally > 1048576) > Batches: 1 (originally 1) Memory Usage: 376885kB" > " Buffers: shared hit=5133174 > read=976786 dirtied=780" > " -> Merge Left Join > (cost=10304076.40..10315637.55 rows=886647 width=130) (actual > time=1862979.687..1865773.765 rows=3362294 loops=1)" > " Output: > ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, > att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value, att (...)" > " Merge Cond: ((ba.env_id = > loc2.env_id) AND (((att_loc_hecho.ent_inst_att_str_value)::integer) = > loc2.bus_ent_inst_name_num))" > " Buffers: shared hit=5133174 > read=976786 dirtied=780" > " -> Sort > (cost=10178591.32..10180807.94 rows=886647 width=141) (actual > time=1862965.240..1863856.321 rows=3362294 loops=1)" > " Output: > ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_loc_hecho.ent_inst_att_str_value, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_st (...)" > " Sort Key: ba.env_id, > ((att_loc_hecho.ent_inst_att_str_value)::integer)" > " Sort Method: quicksort > Memory: 544870kB" > " Buffers: shared > hit=5133062 read=976781 dirtied=780" > " -> Merge Left Join > (cost=10079438.31..10090999.47 rows=886647 width=141) (actual > time=1854085.484..1857592.771 rows=3362294 loops=1)" > " Output: > ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_loc_hecho.ent_inst_att_str_value, att_call.ent_inst_att_str_value, > att_nro.ent_inst_att_str_value, att_bis.ent_inst_ (...)" > " Merge Cond: > ((ba.env_id = depto2.env_id) AND > (((att_dir_hecho.ent_inst_att_str_value)::integer) = > depto2.bus_ent_inst_name_num))" > " Buffers: shared > hit=5133062 read=976781 dirtied=780" > " -> Sort > (cost=9953953.24..9956169.85 rows=886647 width=152) (actual > time=1854079.630..1855329.406 rows=3362294 loops=1)" > " Output: > ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_dir_hecho.ent_inst_att_str_value, att_loc_hecho.ent_inst_att_str_value, > att_call.ent_inst_att_str_value, att_n (...)" > " Sort Key: > ba.env_id, ((att_dir_hecho.ent_inst_att_str_value)::integer)" > " Sort Method: > quicksort Memory: 544857kB" > " Buffers: > shared hit=5133055 read=976779 dirtied=780" > " -> Hash > Right Join (cost=9791232.05..9866361.38 rows=886647 width=152) (actual > time=1844734.652..1849217.758 rows=3362294 loops=1)" > " > Output: > ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, ba.att_value_num_1, > att_dir_hecho.ent_inst_att_str_value, att_loc_hecho.ent_inst_att_str_value, > att_call.ent_inst_att_str_value, (...)" > " Hash > Cond: ((att_rut.env_id = ba.env_id) AND (att_rut.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > Buffers: shared hit=5133055 read=976779 dirtied=780" > " -> > Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_rut (cost=0.83..72690.43 rows=319036 > width=24) (actual time=17.325..3078.312 rows=149644 loops=1)" > " > Output: att_rut.att_id, att_rut.ent_inst_att_str_value, att_rut.env_id, > att_rut.bus_ent_inst_id, att_rut.reg_status" > " > Index Cond: (att_rut.att_id = 1138)" > " > Heap Fetches: 5299" > " > Buffers: shared hit=26350 read=1137" > " -> > Hash (cost=9777931.51..9777931.51 rows=886647 width=136) (actual > time=1844713.350..1844713.350 rows=3362294 loops=1)" > " > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_value, att_call.ent_inst_att_str_ (...)" > " > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 329015kB" > " > Buffers: shared hit=5106705 read=975642 dirtied=780" > " > -> Hash Right Join (cost=9705206.15..9777931.51 rows=886647 width=136) > (actual time=1837569.880..1842945.853 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_value, att_call.ent_inst_at (...)" > " > > Hash Cond: ((att_km.env_id = ba.env_id) AND (att_km.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=5106705 read=975642 dirtied=780" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_02 on > public.bus_ent_inst_attribute att_km (cost=0.70..70286.34 rows=319036 > width=13) (actual time=0.107..2995.494 rows=149942 l (...)" > " > > Output: att_km.att_id, att_km.ent_inst_att_num_value, att_km.env_id, > att_km.bus_ent_inst_id, att_km.reg_status" > " > > Index Cond: (att_km.att_id = 1132)" > " > > Heap Fetches: 5330" > " > > Buffers: shared hit=59470 read=1171" > " > > -> Hash (cost=9691905.74..9691905.74 rows=886647 width=131) (actual > time=1837565.949..1837565.949 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_value, att_call.ent_i (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 328650kB" > " > > Buffers: shared hit=5047235 read=974471 dirtied=780" > " > > -> Hash Right Join (cost=7694366.79..9691905.74 rows=886647 width=131) > (actual time=1710903.369..1834807.221 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_value, att_call (...)" > " > > Hash Cond: ((att_bis.env_id = ba.env_id) AND (att_bis.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=5047235 read=974471 dirtied=780" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_bis (cost=0.83..1932476.93 rows=8508077 > width=24) (actual time=6.488..116892 (...)" > " > > Output: att_bis.att_id, att_bis.ent_inst_att_str_value, att_bis.env_id, > att_bis.bus_ent_inst_id, att_bis.reg_status" > " > > Index Cond: (att_bis.att_id = 1117)" > " > > Heap Fetches: 228123" > " > > Buffers: shared hit=218185 read=52064 dirtied=27" > " > > -> Hash (cost=7681066.26..7681066.26 rows=886647 width=115) (actual > time=1710893.007..1710893.007 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_value, at (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 309513kB" > " > > Buffers: shared hit=4829050 read=922407 dirtied=753" > " > > -> Hash Right Join (cost=5969990.07..7681066.26 rows=886647 width=115) > (actual time=1566042.427..1708291.649 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_str_val (...)" > " > > Hash Cond: ((att_call.env_id = ba.env_id) AND (att_call.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=4829050 read=922407 dirtied=753" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_call (cost=0.83..1655345.90 rows=7287794 > width=24) (actual time= (...)" > " > > Output: att_call.att_id, att_call.ent_inst_att_str_value, att_call.env_id, > att_call.bus_ent_inst_id, att_call.reg_status" > " > > Index Cond: (att_call.att_id = 1119)" > " > > Heap Fetches: 213801" > " > > Buffers: shared hit=1852588 read=60151 dirtied=23" > " > > -> Hash (cost=5956689.54..5956689.54 rows=886647 width=99) (actual > time=1566015.832..1566015.832 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst_att_s (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 258291kB" > " > > Buffers: shared hit=2976462 read=862256 dirtied=730" > " > > -> Hash Right Join (cost=4253571.63..5956689.54 rows=886647 width=99) > (actual time=1355922.435..1563760.249 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_dir_hecho.ent_inst_att_str_value, > att_loc_hecho.ent_inst (...)" > " > > Hash Cond: ((att_dir_hecho.env_id = ba.env_id) AND > (att_dir_hecho.bus_ent_inst_id = ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=2976462 read=862256 dirtied=730" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_dir_hecho (cost=0.83..1647646.84 > rows=7253898 width= (...)" > " > > Output: att_dir_hecho.att_id, att_dir_hecho.ent_inst_att_str_value, > att_dir_hecho.env_id, att_dir_hecho.bus_ent_inst_id, att_dir_hecho (...)" > " > > Index Cond: (att_dir_hecho.att_id = 1122)" > " > > Heap Fetches: 221189" > " > > Buffers: shared hit=217265 read=76872 dirtied=96" > " > > -> Hash (cost=4240271.10..4240271.10 rows=886647 width=83) (actual > time=1355910.157..1355910.157 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_loc_hecho.ent_inst_att_str_value, att_nro.ent_inst > (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 224784kB" > " > > Buffers: shared hit=2759197 read=785384 dirtied=634" > " > > -> Hash Right Join (cost=2672428.25..4240271.10 rows=886647 width=83) > (actual time=1097647.410..1353630.001 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_loc_hecho.ent_inst_att_str_value, att_nro.en (...)" > " > > Hash Cond: ((att_loc_hecho.env_id = ba.env_id) AND > (att_loc_hecho.bus_ent_inst_id = ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=2759197 read=785384 dirtied=634" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_loc_hecho (cost=0.83..1516778.41 rows=66 > (...)" > " > > Output: att_loc_hecho.att_id, att_loc_hecho.ent_inst_att_str_value, > att_loc_hecho.env_id, att_loc_hecho.bus_ent_inst_id, a (...)" > " > > Index Cond: (att_loc_hecho.att_id = 1133)" > " > > Heap Fetches: 218787" > " > > Buffers: shared hit=332968 read=93935 dirtied=115" > " > > -> Hash (cost=2659127.72..2659127.72 rows=886647 width=67) (actual > time=1097642.027..1097642.027 rows=3362294 loops=1)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_nro.ent_inst_att_str_value, att_pad.en (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 215839kB" > " > > Buffers: shared hit=2426229 read=691449 dirtied=519" > " > > -> Hash Right Join (cost=1353880.71..2659127.72 rows=886647 width=67) > (actual time=466534.722..1095259.942 rows=3362294 (...)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_nro.ent_inst_att_str_value, att_ (...)" > " > > Hash Cond: ((att_nro.env_id = ba.env_id) AND (att_nro.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > > Buffers: shared hit=2426229 read=691449 dirtied=519" > " > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_nro (cost=0.83..1262736.66 r (...)" > " > > Output: att_nro.att_id, att_nro.ent_inst_att_str_value, att_nro.env_id, > att_nro.bus_ent_inst_id, att_nro.reg_s (...)" > " > > Index Cond: (att_nro.att_id = 1135)" > " > > Heap Fetches: 156988" > " > > Buffers: shared hit=1568458 read=151792 dirtied=285" > " > > -> Hash (cost=1340580.18..1340580.18 rows=886647 width=51) (actual > time=466528.985..466528.985 rows=3362294 loops= (...)" > " > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_pad.ent_inst_att_str_value (...)" > " > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 204115kB" > " > > Buffers: shared hit=857771 read=539657 dirtied=234" > " > > -> Hash Right Join (cost=1265450.85..1340580.18 rows=886647 width=51) > (actual time=464578.744..465343.707 ro (...)" > " > > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_pad.ent_inst_att_str (...)" > " > > > Hash Cond: ((att_manz.env_id = ba.env_id) AND (att_manz.bus_ent_inst_id = > ba.bus_ent_inst_id_auto))" > " > > > Buffers: shared hit=857771 read=539657 dirtied=234" > " > > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_manz (cost=0.83. (...)" > " > > > Output: att_manz.att_id, att_manz.ent_inst_att_str_value, att_manz.env_id, > att_manz.bus_ent_inst_i (...)" > " > > > Index Cond: (att_manz.att_id = 1134)" > " > > > Heap Fetches: 14" > " > > > Buffers: shared hit=276 read=15" > " > > > -> Hash (cost=1252150.32..1252150.32 rows=886647 width=35) (actual > time=464569.271..464569.271 rows=33 (...)" > " > > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_pad.ent_inst_a (...)" > " > > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 204114kB" > " > > > Buffers: shared hit=857495 read=539642 dirtied=234" > " > > > -> Hash Right Join (cost=1177020.99..1252150.32 rows=886647 width=35) > (actual time=184587.973..4 (...)" > " > > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1, att_pad.ent_ (...)" > " > > > Hash Cond: ((att_pad.env_id = ba.env_id) AND (att_pad.bus_ent_inst_id = > ba.bus_ent_inst_id_a (...)" > " > > > Buffers: shared hit=857495 read=539642 dirtied=234" > " > > > -> Index Only Scan using ix_bus_ent_inst_attr_03 on > public.bus_ent_inst_attribute att_pad (...)" > " > > > Output: att_pad.att_id, att_pad.ent_inst_att_str_value, att_pad.env_id, > att_pad.bus_en (...)" > " > > > Index Cond: (att_pad.att_id = 1136)" > " > > > Heap Fetches: 54024" > " > > > Buffers: shared hit=334762 read=60835 dirtied=136" > " > > > -> Hash (cost=1163720.45..1163720.45 rows=886647 width=19) (actual > time=184573.023..184573 (...)" > " > > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1" > " > > > Buckets: 4194304 (originally 1048576) Batches: 1 (originally 1) Memory > Usage: 200216 (...)" > " > > > Buffers: shared hit=522733 read=478807 dirtied=98" > " > > > -> Bitmap Heap Scan on public.bus_ent_instance ba > (cost=35242.83..1163720.45 rows=88 (...)" > " > > > Output: ba.bus_ent_inst_id_auto, ba.att_value_1, ba.env_id, > ba.att_value_num_1" > " > > > Recheck Cond: ((((ba.bus_ent_inst_name_pre)::text = 'FOTPER'::text) AND > ((ba.att (...)" > " > > > Heap Blocks: exact=981056" > " > > > Buffers: shared hit=522733 read=478807 dirtied=98" > " > > > -> BitmapOr (cost=35242.83..35242.83 rows=896239 width=0) (actual > time=33401.6 (...)" > " > > > Buffers: shared hit=43 read=20441" > " > > > -> Bitmap Index Scan on ix_bus_ent_instance_atts_namenum_01 (cost=0.00.. > (...)" > " > > > Index Cond: (((ba.bus_ent_inst_name_pre)::text = 'FOTPER'::text) AND (...)" > " > > > Buffers: shared hit=9 read=5030" > " > > > -> Bitmap Index Scan on ix_bus_ent_instance_atts_namenum_01 (cost=0.00.. > (...)" > " > > > Index Cond: (((ba.bus_ent_inst_name_pre)::text = 'FOTPER'::text) AND (...)" > " > > > Buffers: shared hit=34 read=15411" > " -> Sort > (cost=125485.07..125573.87 rows=35520 width=13) (actual time=5.831..312.164 > rows=3217523 loops=1)" > " Output: > depto2.att_value_1, depto2.env_id, depto2.bus_ent_inst_name_num" > " Sort Key: > depto2.env_id, depto2.bus_ent_inst_name_num" > " Sort Method: > quicksort Memory: 26kB" > " Buffers: > shared hit=7 read=2" > " -> Bitmap > Heap Scan on public.bus_ent_instance depto2 (cost=971.85..122800.41 > rows=35520 width=13) (actual time=5.758..5.776 rows=21 loops=1)" > " > Output: > depto2.att_value_1, depto2.env_id, depto2.bus_ent_inst_name_num" > " > Recheck > Cond: (depto2.bus_ent_id = 1091)" > " Heap > Blocks: exact=5" > " > Buffers: shared hit=7 read=2" > " -> > Bitmap Index Scan on ix_bus_ent_instance_01 (cost=0.00..962.97 rows=35520 > width=0) (actual time=5.733..5.733 rows=21 loops=1)" > " > Index Cond: (depto2.bus_ent_id = 1091)" > " > Buffers: shared hit=2 read=2" > " -> Sort > (cost=125485.07..125573.87 rows=35520 width=13) (actual > time=14.418..320.637 > rows=3217335 loops=1)" > " Output: > loc2.att_value_1, > loc2.env_id, loc2.bus_ent_inst_name_num" > " Sort Key: loc2.env_id, > loc2.bus_ent_inst_name_num" > " Sort Method: quicksort > Memory: 76kB" > " Buffers: shared hit=112 > read=5" > " -> Bitmap Heap Scan on > public.bus_ent_instance loc2 (cost=971.85..122800.41 rows=35520 width=13) > (actual time=13.305..13.922 rows=725 loops=1)" > " Output: > loc2.att_value_1, loc2.env_id, loc2.bus_ent_inst_name_num" > " Recheck Cond: > (loc2.bus_ent_id = 1165)" > " Heap Blocks: > exact=110" > " Buffers: shared > hit=112 read=5" > " -> Bitmap Index > Scan on ix_bus_ent_instance_01 (cost=0.00..962.97 rows=35520 width=0) > (actual time=13.262..13.262 rows=725 loops=1)" > " Index Cond: > (loc2.bus_ent_id = 1165)" > " Buffers: > shared hit=2 read=5" > " -> Hash (cost=4.35..4.35 rows=235 width=552) (actual > time=0.175..0.175 rows=235 loops=1)" > " Output: bi.bus_ent_inst_name_num, bi.att_value_num_7, > bi.att_value_10" > " Buckets: 1024 Batches: 1 Memory Usage: 19kB" > " Buffers: local hit=2" > " -> Seq Scan on pg_temp_179.temp_table bi > (cost=0.00..4.35 rows=235 width=552) (actual time=0.015..0.055 rows=235 > loops=1)" > " Output: bi.bus_ent_inst_name_num, > bi.att_value_num_7, bi.att_value_10" > " Buffers: local hit=2" > " -> Index Scan using ix_bus_ent_inst_attr_01 on > public.bus_ent_inst_attribute a2 (cost=0.58..237.03 rows=123 width=20) > (actual time=23.167..23.168 rows=1 loops=306)" > " Output: a2.env_id, a2.bus_ent_inst_id, a2.att_id, > a2.att_row_id_auto, a2.att_index_id, a2.ent_inst_att_num_value, > a2.ent_inst_att_str_value, a2.ent_inst_att_dte_value, > a2.ent_inst_att_doc_id, a2.ent_inst_att_tran_1, a2.ent_inst_att_tran_2, a2 > (...)" > " Index Cond: ((a2.bus_ent_inst_id = ba.bus_ent_inst_id_auto) > AND (a2.att_id = 1083))" > " Buffers: shared hit=635 read=895" > " SubPlan 1" > " -> Index Scan using ix_bus_ent_inst_attr_01 on > public.bus_ent_inst_attribute a (cost=0.58..141.91 rows=72 width=16) > (actual time=0.646..0.647 rows=1 loops=306)" > " Output: a.ent_inst_att_str_value" > " Index Cond: ((ba.bus_ent_inst_id_auto = a.bus_ent_inst_id) > AND (a.att_id = 1071))" > " Filter: (a.reg_status = 0)" > " Buffers: shared hit=1434 read=31" > " SubPlan 2" > " -> Index Scan using ix_bus_ent_inst_attr_01 on > public.bus_ent_inst_attribute t (cost=0.58..46.15 rows=21 width=16) > (actual > time=0.839..0.841 rows=0 loops=306)" > " Output: t.ent_inst_att_str_value" > " Index Cond: ((ba.bus_ent_inst_id_auto = t.bus_ent_inst_id) > AND (t.att_id = 1141))" > " Filter: (t.reg_status = 0)" > " Buffers: shared hit=1217 read=42" > "Planning time: 18.329 ms" > "Execution time: 1972336.524 ms" > > > > -- > Sent from: > https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html > > >
