explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zwcB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 42,201.770 ↓ 2.0 2 1

Nested Loop Left Join (cost=112,480.16..9,609,597.38 rows=1 width=293) (actual time=40,690.240..42,201.770 rows=2 loops=1)

  • Join Filter: ("*SELECT* 1_1".customer_id = deal_customer.customer_id)
2.          

Initplan (forNested Loop Left Join)

3. 23.686 88.715 ↑ 1.0 1 1

Aggregate (cost=4,875.60..4,875.61 rows=1 width=32) (actual time=88.714..88.715 rows=1 loops=1)

4. 65.029 65.029 ↑ 1.0 173,614 1

Seq Scan on deal_customer deal_customer_1 (cost=0.00..4,441.33 rows=173,708 width=4) (actual time=0.019..65.029 rows=173,614 loops=1)

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24825
5. 0.025 42,111.124 ↓ 2.0 2 1

Nested Loop Left Join (cost=75,277.56..9,572,394.73 rows=1 width=278) (actual time=40,599.598..42,111.124 rows=2 loops=1)

  • Join Filter: ("*SELECT* 1".customer_id = deal_customer.customer_id)
6. 2.570 42,001.085 ↓ 2.0 2 1

Nested Loop Left Join (cost=43,289.29..9,540,406.41 rows=1 width=246) (actual time=40,489.565..42,001.085 rows=2 loops=1)

  • Join Filter: (payment_1.deal_id = deal.id)
  • Rows Removed by Join Filter: 30136
7. 0.009 41,924.319 ↓ 2.0 2 1

Nested Loop Left Join (cost=25,380.40..9,521,779.15 rows=1 width=214) (actual time=40,425.653..41,924.319 rows=2 loops=1)

8. 0.008 41,923.368 ↓ 2.0 2 1

Nested Loop Left Join (cost=25,342.81..9,521,738.78 rows=1 width=217) (actual time=40,424.894..41,923.368 rows=2 loops=1)

  • Join Filter: (aml_report_log.deal_id = deal.id)
  • Rows Removed by Join Filter: 4
9. 0.012 41,923.232 ↓ 2.0 2 1

Nested Loop Left Join (cost=25,330.15..9,521,726.03 rows=1 width=209) (actual time=40,424.767..41,923.232 rows=2 loops=1)

10. 458.275 41,324.368 ↓ 2.0 2 1

Nested Loop (cost=25,321.45..9,521,703.56 rows=1 width=124) (actual time=39,826.088..41,324.368 rows=2 loops=1)

  • Join Filter: (deal.id = deal_state.deal_id)
  • Rows Removed by Join Filter: 1997906
11. 907.933 907.933 ↑ 1.0 998,954 1

Index Scan Backward using deal_state_last_status_state_last_status_created_index on deal_state (cost=0.43..65,051.60 rows=1,001,230 width=16) (actual time=0.082..907.933 rows=998,954 loops=1)

  • Index Cond: (last_status_state = 'DONE'::text)
12. 132.181 39,958.160 ↓ 2.0 2 998,954

Materialize (cost=25,321.02..9,441,633.51 rows=1 width=116) (actual time=0.040..0.040 rows=2 loops=998,954)

13. 105.005 39,825.979 ↓ 2.0 2 1

Merge Left Join (cost=25,321.02..9,441,633.51 rows=1 width=116) (actual time=39,825.765..39,825.979 rows=2 loops=1)

  • Merge Cond: (deal.id = payment.deal_id)
14. 51.911 37,326.992 ↓ 2.0 2 1

Merge Join (cost=25,320.59..9,322,426.06 rows=1 width=84) (actual time=37,326.779..37,326.992 rows=2 loops=1)

  • Merge Cond: (deal.id = deal_row.deal_id)
15. 1,201.815 1,201.815 ↓ 3.0 3 1

Index Scan using deal_pkey on deal (cost=0.43..101,515.04 rows=1 width=16) (actual time=1,201.802..1,201.815 rows=3 loops=1)

  • Filter: ((created >= '2019-01-10 01:00:00+02'::timestamp with time zone) AND (created < '2019-01-12 01:00:00+02'::timestamp with time zone) AND (agent_id = 4) AND (type = 'CUSTOMER'::text))
  • Rows Removed by Filter: 1082397
16. 689.598 36,073.266 ↑ 2.6 86,644 1

GroupAggregate (cost=25,320.17..9,218,111.71 rows=223,944 width=68) (actual time=391.418..36,073.266 rows=86,644 loops=1)

  • Group Key: deal_row.deal_id
17. 225.175 35,383.668 ↑ 1.1 200,369 1

Nested Loop Left Join (cost=25,320.17..9,211,953.25 rows=223,944 width=15) (actual time=386.905..35,383.668 rows=200,369 loops=1)

18. 277.256 3,299.822 ↑ 1.1 200,369 1

Nested Loop (cost=25,282.58..171,893.83 rows=223,944 width=19) (actual time=379.991..3,299.822 rows=200,369 loops=1)

  • Join Filter: (deal_1.id = deal_row.deal_id)
19. 227.761 1,722.906 ↓ 1.1 86,644 1

Merge Join (cost=25,282.15..95,332.24 rows=81,991 width=12) (actual time=379.926..1,722.906 rows=86,644 loops=1)

  • Merge Cond: (deal_state_1.deal_id = deal_1.id)
20. 1,259.811 1,259.811 ↑ 1.0 998,954 1

Index Scan using deal_state_pkey on deal_state deal_state_1 (cost=0.43..66,285.62 rows=1,001,230 width=8) (actual time=0.087..1,259.811 rows=998,954 loops=1)

  • Filter: (last_status_state = 'DONE'::text)
  • Rows Removed by Filter: 83446
21. 151.599 235.334 ↓ 1.0 91,382 1

Sort (cost=25,281.70..25,503.29 rows=88,637 width=4) (actual time=130.226..235.334 rows=91,382 loops=1)

  • Sort Key: deal_1.id
  • Sort Method: external merge Disk: 1256kB
22. 69.494 83.735 ↓ 1.0 91,382 1

Bitmap Heap Scan on deal deal_1 (cost=2,043.28..17,997.68 rows=88,637 width=4) (actual time=17.470..83.735 rows=91,382 loops=1)

  • Recheck Cond: (agent_id = 4)
  • Filter: (type = 'CUSTOMER'::text)
  • Rows Removed by Filter: 1837
  • Heap Blocks: exact=14215
23. 14.241 14.241 ↑ 1.0 93,234 1

Bitmap Index Scan on deal_agent_id_office_id_index (cost=0.00..2,021.12 rows=94,493 width=0) (actual time=14.241..14.241 rows=93,234 loops=1)

  • Index Cond: (agent_id = 4)
24. 1,299.660 1,299.660 ↑ 4.5 2 86,644

Index Scan using deal_row_deal_id_idx on deal_row (cost=0.43..0.82 rows=9 width=15) (actual time=0.008..0.015 rows=2 loops=86,644)

  • Index Cond: (deal_id = deal_state_1.deal_id)
25. 400.738 31,858.671 ↓ 0.0 0 200,369

Limit (cost=37.59..40.35 rows=1 width=1) (actual time=0.159..0.159 rows=0 loops=200,369)

26. 787.527 31,457.933 ↓ 0.0 0 200,369

Hash Join (cost=37.59..40.35 rows=1 width=1) (actual time=0.157..0.157 rows=0 loops=200,369)

  • Hash Cond: (list_suspicious_aml_indicators.id = deal_aml_indicator.aml_indicator_id)
27. 7.531 13.949 ↑ 2.0 101 377

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.019..0.037 rows=101 loops=377)

  • Group Key: list_suspicious_aml_indicators.id
28. 6.418 6.418 ↑ 5.2 194 1

Function Scan on list_suspicious_aml_indicators (cost=0.25..10.25 rows=1,000 width=4) (actual time=6.376..6.418 rows=194 loops=1)

29. 801.476 30,656.457 ↓ 0.0 0 200,369

Hash (cost=24.83..24.83 rows=1 width=4) (actual time=0.153..0.153 rows=0 loops=200,369)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 29,854.981 29,854.981 ↓ 0.0 0 200,369

Seq Scan on deal_aml_indicator (cost=0.00..24.83 rows=1 width=4) (actual time=0.148..0.149 rows=0 loops=200,369)

  • Filter: (deal_aml_id = deal_state_1.last_deal_aml_id)
  • Rows Removed by Filter: 1282
31. 1,250.384 2,393.982 ↓ 1.0 968,582 1

GroupAggregate (cost=0.43..107,354.81 rows=948,210 width=36) (actual time=0.097..2,393.982 rows=968,582 loops=1)

  • Group Key: payment.deal_id
32. 1,143.598 1,143.598 ↓ 1.0 1,372,951 1

Index Scan using payment_deal_id_index on payment (cost=0.43..88,646.86 rows=1,371,065 width=9) (actual time=0.088..1,143.598 rows=1,372,951 loops=1)

  • Index Cond: (deal_id IS NOT NULL)
  • Filter: (type = ANY ('{CASH,CARD}'::text[]))
  • Rows Removed by Filter: 38583
33. 4.742 598.852 ↓ 0.0 0 2

Hash Right Join (cost=8.70..22.46 rows=1 width=89) (actual time=299.362..299.426 rows=0 loops=2)

  • Hash Cond: (list_customer_by_id.id = deal_customer.customer_id)
34. 594.008 594.008 ↓ 43.1 43,098 1

Function Scan on list_customer_by_id (cost=0.25..10.25 rows=1,000 width=68) (actual time=591.068..594.008 rows=43,098 loops=1)

35. 0.006 0.102 ↓ 0.0 0 2

Hash (cost=8.44..8.44 rows=1 width=25) (actual time=0.051..0.051 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
36. 0.096 0.096 ↓ 0.0 0 2

Index Scan using deal_customer_deal_id_ndx on deal_customer (cost=0.42..8.44 rows=1 width=25) (actual time=0.047..0.048 rows=0 loops=2)

  • Index Cond: (deal_id = deal.id)
37. 0.004 0.128 ↑ 1.5 2 2

Unique (cost=12.66..12.68 rows=3 width=12) (actual time=0.062..0.064 rows=2 loops=2)

38. 0.054 0.124 ↑ 1.0 3 2

Sort (cost=12.66..12.67 rows=3 width=12) (actual time=0.062..0.062 rows=3 loops=2)

  • Sort Key: aml_report_log.deal_id DESC, aml_report_log.reported DESC
  • Sort Method: quicksort Memory: 25kB
39. 0.070 0.070 ↓ 1.7 5 1

Index Scan using aml_report_log_agent_id_reported_index on aml_report_log (cost=0.28..12.64 rows=3 width=12) (actual time=0.045..0.070 rows=5 loops=1)

  • Index Cond: (agent_id = 4)
40. 0.012 0.942 ↑ 1.0 1 2

Limit (cost=37.59..40.35 rows=1 width=1) (actual time=0.468..0.471 rows=1 loops=2)

41. 0.024 0.930 ↑ 1.0 1 2

Hash Join (cost=37.59..40.35 rows=1 width=1) (actual time=0.465..0.465 rows=1 loops=2)

  • Hash Cond: (list_suspicious_aml_indicators_1.id = deal_aml_indicator_1.aml_indicator_id)
42. 0.078 0.600 ↑ 3.9 51 2

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.290..0.300 rows=51 loops=2)

  • Group Key: list_suspicious_aml_indicators_1.id
43. 0.522 0.522 ↑ 5.2 194 1

Function Scan on list_suspicious_aml_indicators list_suspicious_aml_indicators_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.509..0.522 rows=194 loops=1)

44. 0.012 0.306 ↓ 2.0 2 2

Hash (cost=24.83..24.83 rows=1 width=8) (actual time=0.152..0.153 rows=2 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.294 0.294 ↓ 2.0 2 2

Seq Scan on deal_aml_indicator deal_aml_indicator_1 (cost=0.00..24.83 rows=1 width=8) (actual time=0.146..0.147 rows=2 loops=2)

  • Filter: (deal_aml_id = deal_state.last_deal_aml_id)
  • Rows Removed by Filter: 1280
46. 34.193 74.196 ↑ 1.4 15,068 2

HashAggregate (cost=17,908.89..18,165.45 rows=20,525 width=36) (actual time=27.723..37.098 rows=15,068 loops=2)

  • Group Key: payment_1.deal_id
47. 40.003 40.003 ↑ 1.0 19,681 1

Index Scan using payment_type_index on payment payment_1 (cost=0.43..17,805.89 rows=20,600 width=9) (actual time=0.057..40.003 rows=19,681 loops=1)

  • Index Cond: (type = 'TRANSFER'::text)
  • Filter: (deal_id IS NOT NULL)
  • Rows Removed by Filter: 1
48. 0.002 110.014 ↓ 0.0 0 2

GroupAggregate (cost=31,988.28..31,988.30 rows=1 width=36) (actual time=55.007..55.007 rows=0 loops=2)

  • Group Key: "*SELECT* 1".customer_id
49. 0.010 110.012 ↓ 0.0 0 2

Sort (cost=31,988.28..31,988.28 rows=1 width=22) (actual time=55.006..55.006 rows=0 loops=2)

  • Sort Key: "*SELECT* 1".customer_id
  • Sort Method: quicksort Memory: 25kB
50. 0.014 110.002 ↓ 0.0 0 1

Hash Anti Join (cost=22,443.03..31,988.27 rows=1 width=22) (actual time=110.002..110.002 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".deal_id = aml_report_log_1.deal_id)
51. 0.004 109.964 ↑ 2.0 1 1

Append (cost=22,430.35..31,975.58 rows=2 width=26) (actual time=71.942..109.964 rows=1 loops=1)

52. 0.002 71.941 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=22,430.35..22,430.39 rows=1 width=40) (actual time=71.940..71.941 rows=1 loops=1)

53. 0.018 71.939 ↑ 1.0 1 1

GroupAggregate (cost=22,430.35..22,430.38 rows=1 width=45) (actual time=71.939..71.939 rows=1 loops=1)

  • Group Key: payment_2.customer_id, payment_2.deal_id, payment_2.type
54. 0.049 71.921 ↑ 1.0 1 1

Sort (cost=22,430.35..22,430.36 rows=1 width=18) (actual time=71.920..71.921 rows=1 loops=1)

  • Sort Key: payment_2.customer_id, payment_2.deal_id, payment_2.type
  • Sort Method: quicksort Memory: 25kB
55. 57.943 71.872 ↑ 1.0 1 1

Bitmap Heap Scan on payment payment_2 (cost=2,913.04..22,430.34 rows=1 width=18) (actual time=71.871..71.872 rows=1 loops=1)

  • Recheck Cond: (agent_id = 4)
  • Filter: ((customer_id IS NOT NULL) AND (deal_id IS NOT NULL) AND (last_status_created >= '2019-01-10 01:00:00+02'::timestamp with time zone) AND (last_status_created < '2019-01-12 01:00:00+02'::timestamp with time zone) AND (last_status_state = 'DONE'::text))
  • Rows Removed by Filter: 134258
  • Heap Blocks: exact=3895
56. 13.929 13.929 ↑ 1.0 134,261 1

Bitmap Index Scan on payment_agent_id_office_id_index (cost=0.00..2,913.04 rows=135,015 width=0) (actual time=13.929..13.929 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
57. 0.001 38.019 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=7,914.27..9,545.18 rows=1 width=13) (actual time=38.019..38.019 rows=0 loops=1)

58. 4.684 38.018 ↓ 0.0 0 1

Bitmap Heap Scan on payment payment_3 (cost=7,914.27..9,545.17 rows=1 width=45) (actual time=38.018..38.018 rows=0 loops=1)

  • Recheck Cond: ((deal_id IS NULL) AND (agent_id = 4) AND (customer_id IS NOT NULL))
  • Filter: ((last_status_created >= '2019-01-10 01:00:00+02'::timestamp with time zone) AND (last_status_created < '2019-01-12 01:00:00+02'::timestamp with time zone) AND (last_status_state = 'DONE'::text))
  • Rows Removed by Filter: 13725
  • Heap Blocks: exact=329
59. 0.284 33.334 ↓ 0.0 0 1

BitmapAnd (cost=7,914.27..7,914.27 rows=469 width=0) (actual time=33.334..33.334 rows=0 loops=1)

60. 3.435 3.435 ↑ 1.0 37,132 1

Bitmap Index Scan on payment_deal_id_index (cost=0.00..843.27 rows=37,713 width=0) (actual time=3.435..3.435 rows=37,132 loops=1)

  • Index Cond: (deal_id IS NULL)
61. 9.044 9.044 ↑ 1.0 134,261 1

Bitmap Index Scan on payment_agent_id_office_id_index (cost=0.00..2,913.04 rows=135,015 width=0) (actual time=9.044..9.044 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
62. 20.571 20.571 ↑ 1.0 189,003 1

Bitmap Index Scan on payment_customer_id_index (cost=0.00..4,157.45 rows=193,203 width=0) (actual time=20.571..20.571 rows=189,003 loops=1)

  • Index Cond: (customer_id IS NOT NULL)
63. 0.004 0.024 ↓ 1.7 5 1

Hash (cost=12.64..12.64 rows=3 width=4) (actual time=0.024..0.024 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.020 0.020 ↓ 1.7 5 1

Index Scan using aml_report_log_agent_id_reported_index on aml_report_log aml_report_log_1 (cost=0.28..12.64 rows=3 width=4) (actual time=0.016..0.020 rows=5 loops=1)

  • Index Cond: (agent_id = 4)
65. 0.002 90.636 ↓ 0.0 0 2

GroupAggregate (cost=32,326.99..32,327.01 rows=1 width=36) (actual time=45.318..45.318 rows=0 loops=2)

  • Group Key: "*SELECT* 1_1".customer_id
66. 0.009 90.634 ↓ 0.0 0 2

Sort (cost=32,326.99..32,326.99 rows=1 width=22) (actual time=45.317..45.317 rows=0 loops=2)

  • Sort Key: "*SELECT* 1_1".customer_id
  • Sort Method: quicksort Memory: 25kB
67. 0.000 90.625 ↓ 0.0 0 1

Hash Anti Join (cost=22,780.56..32,326.98 rows=1 width=22) (actual time=90.625..90.625 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1_1".deal_id = aml_report_log_2.deal_id)
68. 0.004 90.625 ↓ 0.0 0 1

Append (cost=22,767.89..32,314.29 rows=2 width=26) (actual time=90.625..90.625 rows=0 loops=1)

69. 0.001 60.840 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=22,767.89..22,767.93 rows=1 width=40) (actual time=60.840..60.840 rows=0 loops=1)

70. 0.013 60.839 ↓ 0.0 0 1

GroupAggregate (cost=22,767.89..22,767.92 rows=1 width=45) (actual time=60.839..60.839 rows=0 loops=1)

  • Group Key: payment_4.customer_id, payment_4.deal_id, payment_4.type
71. 0.007 60.826 ↓ 0.0 0 1

Sort (cost=22,767.89..22,767.89 rows=1 width=18) (actual time=60.825..60.826 rows=0 loops=1)

  • Sort Key: payment_4.customer_id, payment_4.deal_id, payment_4.type
  • Sort Method: quicksort Memory: 25kB
72. 51.679 60.819 ↓ 0.0 0 1

Bitmap Heap Scan on payment payment_4 (cost=2,913.04..22,767.88 rows=1 width=18) (actual time=60.818..60.819 rows=0 loops=1)

  • Recheck Cond: (agent_id = 4)
  • Filter: ((customer_id IS NOT NULL) AND (deal_id IS NOT NULL) AND (last_status_created >= '2019-01-10 01:00:00+02'::timestamp with time zone) AND (last_status_created < '2019-01-12 01:00:00+02'::timestamp with time zone) AND (type = ANY ('{CASH,CARD}'::text[])) AND (last_status_state = 'DONE'::text))
  • Rows Removed by Filter: 134259
  • Heap Blocks: exact=3895
73. 9.140 9.140 ↑ 1.0 134,261 1

Bitmap Index Scan on payment_agent_id_office_id_index (cost=0.00..2,913.04 rows=135,015 width=0) (actual time=9.140..9.140 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
74. 0.001 29.781 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=7,914.27..9,546.36 rows=1 width=13) (actual time=29.781..29.781 rows=0 loops=1)

75. 4.645 29.780 ↓ 0.0 0 1

Bitmap Heap Scan on payment payment_5 (cost=7,914.27..9,546.35 rows=1 width=45) (actual time=29.780..29.780 rows=0 loops=1)

  • Recheck Cond: ((deal_id IS NULL) AND (agent_id = 4) AND (customer_id IS NOT NULL))
  • Filter: ((last_status_created >= '2019-01-10 01:00:00+02'::timestamp with time zone) AND (last_status_created < '2019-01-12 01:00:00+02'::timestamp with time zone) AND (type = ANY ('{CASH,CARD}'::text[])) AND (last_status_state = 'DONE'::text))
  • Rows Removed by Filter: 13725
  • Heap Blocks: exact=329
76. 0.274 25.135 ↓ 0.0 0 1

BitmapAnd (cost=7,914.27..7,914.27 rows=469 width=0) (actual time=25.135..25.135 rows=0 loops=1)

77. 2.229 2.229 ↑ 1.0 37,132 1

Bitmap Index Scan on payment_deal_id_index (cost=0.00..843.27 rows=37,713 width=0) (actual time=2.229..2.229 rows=37,132 loops=1)

  • Index Cond: (deal_id IS NULL)
78. 8.915 8.915 ↑ 1.0 134,261 1

Bitmap Index Scan on payment_agent_id_office_id_index (cost=0.00..2,913.04 rows=135,015 width=0) (actual time=8.915..8.915 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
79. 13.717 13.717 ↑ 1.0 189,003 1

Bitmap Index Scan on payment_customer_id_index (cost=0.00..4,157.45 rows=193,203 width=0) (actual time=13.717..13.717 rows=189,003 loops=1)

  • Index Cond: (customer_id IS NOT NULL)
80. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.64..12.64 rows=3 width=4) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Index Scan using aml_report_log_agent_id_reported_index on aml_report_log aml_report_log_2 (cost=0.28..12.64 rows=3 width=4) (never executed)

  • Index Cond: (agent_id = 4)