explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KD86

Settings
# exclusive inclusive rows x rows loops node
1. 2.173 4,044.708 ↑ 5.4 15 1

GroupAggregate (cost=76,600.45..1,844,196.75 rows=81 width=473) (actual time=4,004.132..4,044.708 rows=15 loops=1)

  • Group Key: est.est_establishment_id, use.use_legal_document, (date_trunc('month'::text, tra.tra_date_apresentation)), pedido_pos.pro_pos_terminal_count, bua.bua_name
  • Functions: 312
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 52.088 ms, Inlining 244.889 ms, Optimization 4288.641 ms, Emission 2219.595 ms, Total 6805.213 ms
2. 0.396 4,000.598 ↓ 4.3 347 1

Sort (cost=76,600.45..76,600.65 rows=81 width=155) (actual time=3,999.903..4,000.598 rows=347 loops=1)

  • Sort Key: est.est_establishment_id, use.use_legal_document, (date_trunc('month'::text, tra.tra_date_apresentation)), pedido_pos.pro_pos_terminal_count, bua.bua_name
  • Sort Method: quicksort Memory: 117kB
3. 0.198 4,000.202 ↓ 4.3 347 1

Hash Left Join (cost=2,144.49..76,597.88 rows=81 width=155) (actual time=3,999.352..4,000.202 rows=347 loops=1)

  • Hash Cond: (est.est_establishment_id = pedido_pos.ure_est_establishment_id)
4. 1,533.054 3,997.281 ↓ 4.3 347 1

Gather (cost=1,862.85..76,315.83 rows=81 width=143) (actual time=3,996.609..3,997.281 rows=347 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.066 2,464.227 ↓ 3.4 116 3 / 3

Nested Loop (cost=862.85..75,307.73 rows=34 width=143) (actual time=2,461.367..2,464.227 rows=116 loops=3)

6. 0.128 2,463.583 ↓ 3.4 116 3 / 3

Nested Loop (cost=862.57..75,273.72 rows=34 width=140) (actual time=2,461.056..2,463.583 rows=116 loops=3)

7. 6.813 2,462.877 ↓ 3.4 116 3 / 3

Nested Loop (cost=862.42..75,267.67 rows=34 width=113) (actual time=2,460.629..2,462.877 rows=116 loops=3)

8. 12.531 2,381.750 ↓ 1.6 12,386 3 / 3

Nested Loop (cost=862.00..70,768.74 rows=7,749 width=105) (actual time=2,260.497..2,381.750 rows=12,386 loops=3)

9. 8.619 2,307.107 ↓ 1.6 12,422 3 / 3

Hash Join (cost=861.57..47,058.92 rows=7,749 width=105) (actual time=2,260.109..2,307.107 rows=12,422 loops=3)

  • Hash Cond: (mor.mor_establishment_id = est.est_establishment_id)
10. 40.588 43.185 ↑ 1.2 12,422 3 / 3

Parallel Bitmap Heap Scan on cbp_mor_movement_reseller mor (cost=701.11..46,857.71 rows=15,499 width=46) (actual time=4.787..43.185 rows=12,422 loops=3)

  • Recheck Cond: (mor_use_reseller_id = '315'::bigint)
  • Filter: (mor_status <> 2)
  • Rows Removed by Filter: 37
11. 2.597 2.597 ↓ 1.0 37,378 1 / 3

Bitmap Index Scan on ix_cbp_mor_movement_reseller_mor_use_reseller_id (cost=0.00..691.81 rows=37,251 width=0) (actual time=7.790..7.790 rows=37,378 loops=1)

  • Index Cond: (mor_use_reseller_id = '315'::bigint)
12. 0.040 2,255.303 ↑ 17.0 76 3 / 3

Hash (cost=144.31..144.31 rows=1,292 width=67) (actual time=2,255.301..2,255.303 rows=76 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 25kB
13. 2,219.808 2,255.263 ↑ 17.0 76 3 / 3

Seq Scan on jst_est_establishment est (cost=23.01..144.31 rows=1,292 width=67) (actual time=2,254.352..2,255.263 rows=76 loops=3)

  • Filter: (hashed SubPlan 23)
  • Rows Removed by Filter: 2,509
14.          

SubPlan (for Seq Scan)

15. 35.253 35.455 ↑ 1.0 76 3 / 3

Bitmap Heap Scan on jst_eur_establishments_user_resellers (cost=4.87..22.82 rows=76 width=8) (actual time=35.433..35.455 rows=76 loops=3)

  • Recheck Cond: (jst_use_user_use_user_id = '2036'::bigint)
  • Heap Blocks: exact=2
16. 0.202 0.202 ↑ 1.0 76 3 / 3

Bitmap Index Scan on ix_jst_eur_establishments_user_resellers_jst_use_user_use_user_ (cost=0.00..4.85 rows=76 width=0) (actual time=0.202..0.202 rows=76 loops=3)

  • Index Cond: (jst_use_user_use_user_id = '2036'::bigint)
17. 62.112 62.112 ↑ 1.0 1 37,267 / 3

Index Scan using pk_cbp_mov_movement on cbp_mov_movement mov (cost=0.43..3.06 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=37,267)

  • Index Cond: (mov_movement_id = mor.mor_mov_movement_id)
18. 74.314 74.314 ↓ 0.0 0 37,157 / 3

Index Scan using pk_cbp_tra_transaction on cbp_tra_transaction tra (cost=0.43..0.58 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=37,157)

  • Index Cond: (tra_transaction_id = mov.mov_tra_transaction_id)
  • Filter: ((tra_status = 1) AND ((tra_dcol)::text = 'SalePOS'::text) AND (date_trunc('month'::text, tra_date_apresentation) >= date_trunc('month'::text, ('2020-03-01'::date)::timestamp with time zone)) AND (date_trunc('month'::text, tra_date_apresentation) <= date_trunc('month'::text, ('2020-03-30'::date)::timestamp with time zone)))
  • Rows Removed by Filter: 1
19. 0.578 0.578 ↑ 1.0 1 347 / 3

Index Scan using pk_cbp_bua_business_activity on cbp_bua_business_activity bua (cost=0.14..0.18 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=347)

  • Index Cond: (bua_business_activity_id = est.est_bua_business_activity_id)
20. 0.578 0.578 ↑ 1.0 1 347 / 3

Index Scan using pk_jst_use_user on jst_use_user use (cost=0.28..1.00 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=347)

  • Index Cond: (use_user_id = est.est_use_owner_id)
21. 0.139 2.723 ↓ 1.2 761 1

Hash (cost=273.60..273.60 rows=643 width=12) (actual time=2.720..2.723 rows=761 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
22. 0.115 2.584 ↓ 1.2 761 1

Subquery Scan on pedido_pos (cost=263.54..273.60 rows=643 width=12) (actual time=2.302..2.584 rows=761 loops=1)

23. 0.128 2.469 ↓ 1.2 761 1

Unique (cost=263.54..267.17 rows=643 width=2,120) (actual time=2.281..2.469 rows=761 loops=1)

24. 0.494 2.341 ↓ 1.1 784 1

Sort (cost=263.54..265.35 rows=727 width=2,120) (actual time=2.279..2.341 rows=784 loops=1)

  • Sort Key: ure.ure_est_establishment_id, jpp.pro_product_id
  • Sort Method: quicksort Memory: 86kB
25. 0.503 1.847 ↓ 1.1 784 1

Hash Join (cost=76.92..228.98 rows=727 width=2,120) (actual time=0.634..1.847 rows=784 loops=1)

  • Hash Cond: (jpp.pro_product_id = ure.ure_pro_product_id)
26. 0.758 0.758 ↑ 1.0 2,051 1

Seq Scan on jst_pro_product jpp (cost=0.00..146.66 rows=2,051 width=12) (actual time=0.017..0.758 rows=2,051 loops=1)

  • Filter: ((pro_dcol)::text = 'POS'::text)
  • Rows Removed by Filter: 402
27. 0.150 0.586 ↑ 1.0 869 1

Hash (cost=66.06..66.06 rows=869 width=16) (actual time=0.586..0.586 rows=869 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
28. 0.436 0.436 ↑ 1.0 869 1

Seq Scan on jst_ure_user_request ure (cost=0.00..66.06 rows=869 width=16) (actual time=0.010..0.436 rows=869 loops=1)

  • Filter: (ure_status = 2)
  • Rows Removed by Filter: 1,536
29.          

SubPlan (for GroupAggregate)

30. 0.045 0.330 ↓ 0.0 0 15

Limit (cost=16.61..16.62 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=15)

31. 0.120 0.285 ↓ 0.0 0 15

Sort (cost=16.61..16.62 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=15)

  • Sort Key: ure_1.ure_approval_date_time DESC
  • Sort Method: quicksort Memory: 25kB
32. 0.061 0.165 ↓ 0.0 0 15

Nested Loop (cost=0.56..16.60 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=15)

33. 0.075 0.075 ↓ 0.0 0 15

Index Scan using ix_jst_ure_user_request_ure_est_establishment_id on jst_ure_user_request ure_1 (cost=0.28..8.30 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=15)

  • Index Cond: (ure_est_establishment_id = est.est_establishment_id)
  • Filter: (ure_status = 2)
34. 0.029 0.029 ↑ 1.0 1 1

Index Scan using pk_jst_pro_product_pro_product_id on jst_pro_product pro (cost=0.28..8.30 rows=1 width=16) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (pro_product_id = ure_1.ure_pro_product_id)
35. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

36. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

37. 0.038 5.724 ↑ 1.0 1 2

Aggregate (cost=5,388.43..5,388.44 rows=1 width=32) (actual time=2.861..2.862 rows=1 loops=2)

38. 0.006 5.686 ↓ 0.0 0 2

Unique (cost=5,388.41..5,388.42 rows=1 width=14) (actual time=2.842..2.843 rows=0 loops=2)

39. 0.036 5.680 ↓ 0.0 0 2

Sort (cost=5,388.41..5,388.41 rows=1 width=14) (actual time=2.839..2.840 rows=0 loops=2)

  • Sort Key: mor2.mor_movement_reseller_id
  • Sort Method: quicksort Memory: 25kB
40. 5.274 5.644 ↓ 0.0 0 2

Bitmap Heap Scan on cbp_mor_movement_reseller mor2 (cost=32.18..5,388.40 rows=1 width=14) (actual time=2.822..2.822 rows=0 loops=2)

  • Recheck Cond: (mor_establishment_id = est.est_establishment_id)
  • Filter: ((mor_status <> 2) AND ((mor_dcol)::text = 'POI_RENT'::text) AND (date_trunc('month'::text, mor_date_payment_reseller_expected) = '2020-04-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1,092
  • Heap Blocks: exact=1,309
41. 0.370 0.370 ↑ 1.4 1,092 2

Bitmap Index Scan on ix_cbp_mor_movement_reseller_mor_establishment_id (cost=0.00..32.18 rows=1,567 width=0) (actual time=0.185..0.185 rows=1,092 loops=2)

  • Index Cond: (mor_establishment_id = est.est_establishment_id)
42. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=5,388.43..5,388.44 rows=1 width=32) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Unique (cost=5,388.41..5,388.42 rows=1 width=14) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,388.41..5,388.41 rows=1 width=14) (never executed)

  • Sort Key: mor2_1.mor_movement_reseller_id
45. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cbp_mor_movement_reseller mor2_1 (cost=32.18..5,388.40 rows=1 width=14) (never executed)

  • Recheck Cond: (mor_establishment_id = est.est_establishment_id)
  • Filter: ((mor_status <> 2) AND ((mor_dcol)::text = 'POI_RENT'::text) AND (date_trunc('month'::text, mor_date_payment_reseller_expected) = '2020-04-01 00:00:00'::timestamp without time zone))
46. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_cbp_mor_movement_reseller_mor_establishment_id (cost=0.00..32.18 rows=1,567 width=0) (never executed)

  • Index Cond: (mor_establishment_id = est.est_establishment_id)
47. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

48. 0.014 2.516 ↑ 1.0 1 2

Aggregate (cost=5,388.43..5,388.44 rows=1 width=32) (actual time=1.257..1.258 rows=1 loops=2)

49. 0.006 2.502 ↓ 0.0 0 2

Unique (cost=5,388.41..5,388.42 rows=1 width=14) (actual time=1.250..1.251 rows=0 loops=2)

50. 0.042 2.496 ↓ 0.0 0 2

Sort (cost=5,388.41..5,388.41 rows=1 width=14) (actual time=1.248..1.248 rows=0 loops=2)

  • Sort Key: mor2_2.mor_movement_reseller_id
  • Sort Method: quicksort Memory: 25kB
51. 2.202 2.454 ↓ 0.0 0 2

Bitmap Heap Scan on cbp_mor_movement_reseller mor2_2 (cost=32.18..5,388.40 rows=1 width=14) (actual time=1.227..1.227 rows=0 loops=2)

  • Recheck Cond: (mor_establishment_id = est.est_establishment_id)
  • Filter: ((mor_status <> 2) AND ((mor_dcol)::text = 'POI_RENT'::text) AND (date_trunc('month'::text, mor_date_payment_reseller_expected) = '2020-04-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1,092
  • Heap Blocks: exact=1,309
52. 0.252 0.252 ↑ 1.4 1,092 2

Bitmap Index Scan on ix_cbp_mor_movement_reseller_mor_establishment_id (cost=0.00..32.18 rows=1,567 width=0) (actual time=0.126..0.126 rows=1,092 loops=2)

  • Index Cond: (mor_establishment_id = est.est_establishment_id)
53. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=5,388.43..5,388.44 rows=1 width=32) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Unique (cost=5,388.41..5,388.42 rows=1 width=14) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,388.41..5,388.41 rows=1 width=14) (never executed)

  • Sort Key: mor2_3.mor_movement_reseller_id
56. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cbp_mor_movement_reseller mor2_3 (cost=32.18..5,388.40 rows=1 width=14) (never executed)

  • Recheck Cond: (mor_establishment_id = est.est_establishment_id)
  • Filter: ((mor_status <> 2) AND ((mor_dcol)::text = 'POI_RENT'::text) AND (date_trunc('month'::text, mor_date_payment_reseller_expected) = '2020-04-01 00:00:00'::timestamp without time zone))
57. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_cbp_mor_movement_reseller_mor_establishment_id (cost=0.00..32.18 rows=1,567 width=0) (never executed)

  • Index Cond: (mor_establishment_id = est.est_establishment_id)
58. 0.150 29.535 ↑ 1.0 1 15

Result (cost=234.33..234.35 rows=1 width=32) (actual time=1.968..1.969 rows=1 loops=15)

59.          

Initplan (for Result)

60. 0.015 0.015 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=15)

61. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

62. 0.030 29.370 ↑ 1.0 1 15

Limit (cost=0.43..234.31 rows=1 width=12) (actual time=1.958..1.958 rows=1 loops=15)

63. 29.340 29.340 ↑ 850.0 1 15

Index Scan using ix_cbp_tra_transaction_tra_date_apresentation on cbp_tra_transaction tra_1 (cost=0.43..198,801.69 rows=850 width=12) (actual time=1.956..1.956 rows=1 loops=15)

  • Filter: ((tra_est_establishment_id = est.est_establishment_id) AND (tra_status = 1))
  • Rows Removed by Filter: 3,751
64. 0.015 0.015 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=15)

65. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

66. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

67. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

68. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

69. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

70. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

71. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=15)

72. 0.694 2.429 ↑ 1.0 1 347

Aggregate (cost=8.46..8.47 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=347)

73. 1.735 1.735 ↑ 1.0 1 347

Index Scan using pk_cbp_tra_transaction on cbp_tra_transaction t2_1 (cost=0.43..8.45 rows=1 width=5) (actual time=0.004..0.005 rows=1 loops=347)

  • Index Cond: (tra_transaction_id = tra.tra_transaction_id)
  • Filter: ((tra_est_establishment_id = est.est_establishment_id) AND (tra_status = 1))
74. 0.347 1.388 ↑ 1.0 1 347

Aggregate (cost=8.46..8.47 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=347)

75. 1.041 1.041 ↑ 1.0 1 347

Index Scan using pk_cbp_tra_transaction on cbp_tra_transaction t2 (cost=0.43..8.45 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=347)

  • Index Cond: (tra_transaction_id = tra.tra_transaction_id)
  • Filter: ((tra_est_establishment_id = est.est_establishment_id) AND (tra_status = 1))
Execution time : 4,082.181 ms