Re: slow query - Mailing list pgsql-performance
| From | dangal |
|---|---|
| Subject | Re: slow query |
| Date | |
| Msg-id | 1585930703629-0.post@n3.nabble.com Whole thread Raw |
| In response to | Re: slow query (Justin Pryzby <pryzby@telsasoft.com>) |
| Responses |
Re: slow query
|
| List | pgsql-performance |
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
pgsql-performance by date: