Re: slow query

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(dot)gallo(at)gmail(dot)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
>
>
>