explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QCTU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 40,855.913 ↓ 3.0 3 1

Nested Loop Left Join (cost=112,480.16..9,609,597.38 rows=1 width=293) (actual time=39,209.197..40,855.913 rows=3 loops=1)

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

Initplan (forNested Loop Left Join)

3. 22.498 84.661 ↑ 1.0 1 1

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

4. 62.163 62.163 ↑ 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..62.163 rows=173,614 loops=1)

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24825
5. 0.018 40,770.778 ↓ 3.0 3 1

Nested Loop Left Join (cost=75,277.56..9,572,394.73 rows=1 width=197) (actual time=39,124.070..40,770.778 rows=3 loops=1)

  • Join Filter: ("*SELECT* 1".customer_id = deal_customer.customer_id)
6. 4.086 40,666.438 ↓ 3.0 3 1

Nested Loop Left Join (cost=43,289.29..9,540,406.41 rows=1 width=165) (actual time=39,019.744..40,666.438 rows=3 loops=1)

  • Join Filter: (payment_1.deal_id = deal.id)
  • Rows Removed by Join Filter: 48059
7. 0.016 40,596.004 ↓ 3.0 3 1

Nested Loop Left Join (cost=25,380.40..9,521,779.15 rows=1 width=133) (actual time=38,974.553..40,596.004 rows=3 loops=1)

8. 0.013 40,594.803 ↓ 3.0 3 1

Nested Loop Left Join (cost=25,342.81..9,521,738.78 rows=1 width=136) (actual time=38,973.835..40,594.803 rows=3 loops=1)

  • Join Filter: (aml_report_log.deal_id = deal.id)
  • Rows Removed by Join Filter: 5
9. 0.021 40,594.631 ↓ 3.0 3 1

Nested Loop Left Join (cost=25,330.15..9,521,726.03 rows=1 width=128) (actual time=38,973.683..40,594.631 rows=3 loops=1)

10. 315.707 40,159.313 ↓ 3.0 3 1

Nested Loop (cost=25,321.45..9,521,703.56 rows=1 width=124) (actual time=38,546.496..40,159.313 rows=3 loops=1)

  • Join Filter: (deal.id = deal_state.deal_id)
  • Rows Removed by Join Filter: 2996859
11. 884.400 884.400 ↑ 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.058..884.400 rows=998,954 loops=1)

  • Index Cond: (last_status_state = 'DONE'::text)
12. 412.773 38,959.206 ↓ 3.0 3 998,954

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

13. 104.053 38,546.433 ↓ 3.0 3 1

Merge Left Join (cost=25,321.02..9,441,633.51 rows=1 width=116) (actual time=38,545.740..38,546.433 rows=3 loops=1)

  • Merge Cond: (deal.id = payment.deal_id)
14. 52.360 36,093.537 ↓ 3.0 3 1

Merge Join (cost=25,320.59..9,322,426.06 rows=1 width=84) (actual time=36,092.847..36,093.537 rows=3 loops=1)

  • Merge Cond: (deal.id = deal_row.deal_id)
15. 1,162.175 1,162.175 ↓ 4.0 4 1

Index Scan using deal_pkey on deal (cost=0.43..101,515.04 rows=1 width=16) (actual time=1,162.165..1,162.175 rows=4 loops=1)

  • Filter: ((created >= '2019-01-07 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: 1082396
16. 636.480 34,879.002 ↑ 2.6 86,644 1

GroupAggregate (cost=25,320.17..9,218,111.71 rows=223,944 width=68) (actual time=361.739..34,879.002 rows=86,644 loops=1)

  • Group Key: deal_row.deal_id
17. 280.716 34,242.522 ↑ 1.1 200,369 1

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

18. 280.751 3,104.980 ↑ 1.1 200,369 1

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

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

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

  • Merge Cond: (deal_state_1.deal_id = deal_1.id)
20. 1,174.974 1,174.974 ↑ 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.086..1,174.974 rows=998,954 loops=1)

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

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

  • Sort Key: deal_1.id
  • Sort Method: external merge Disk: 1256kB
22. 67.515 80.142 ↓ 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=15.454..80.142 rows=91,382 loops=1)

  • Recheck Cond: (agent_id = 4)
  • Filter: (type = 'CUSTOMER'::text)
  • Rows Removed by Filter: 1837
  • Heap Blocks: exact=14215
23. 12.627 12.627 ↑ 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=12.627..12.627 rows=93,234 loops=1)

  • Index Cond: (agent_id = 4)
24. 1,213.016 1,213.016 ↑ 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.014 rows=2 loops=86,644)

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

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

26. 593.190 30,456.088 ↓ 0.0 0 200,369

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

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

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

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

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

29. 801.476 29,854.981 ↓ 0.0 0 200,369

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

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

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

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

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

  • Group Key: payment.deal_id
32. 1,122.756 1,122.756 ↓ 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.082..1,122.756 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. 9.818 435.297 ↑ 1.0 1 3

Hash Right Join (cost=8.70..22.46 rows=1 width=8) (actual time=145.053..145.099 rows=1 loops=3)

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

Function Scan on list_customer_by_id (cost=0.25..10.25 rows=1,000 width=4) (actual time=209.742..212.672 rows=43,098 loops=2)

35. 0.012 0.135 ↑ 1.0 1 3

Hash (cost=8.44..8.44 rows=1 width=8) (actual time=0.045..0.045 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.123 0.123 ↑ 1.0 1 3

Index Scan using deal_customer_deal_id_ndx on deal_customer (cost=0.42..8.44 rows=1 width=8) (actual time=0.040..0.041 rows=1 loops=3)

  • Index Cond: (deal_id = deal.id)
37. 0.009 0.159 ↑ 1.5 2 3

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

38. 0.024 0.150 ↑ 1.0 3 3

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

  • Sort Key: aml_report_log.deal_id DESC, aml_report_log.reported DESC
  • Sort Method: quicksort Memory: 25kB
39. 0.126 0.126 ↓ 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.100..0.126 rows=5 loops=1)

  • Index Cond: (agent_id = 4)
40. 0.024 1.185 ↑ 1.0 1 3

Limit (cost=37.59..40.35 rows=1 width=1) (actual time=0.391..0.395 rows=1 loops=3)

41. 0.054 1.161 ↑ 1.0 1 3

Hash Join (cost=37.59..40.35 rows=1 width=1) (actual time=0.386..0.387 rows=1 loops=3)

  • Hash Cond: (list_suspicious_aml_indicators_1.id = deal_aml_indicator_1.aml_indicator_id)
42. 0.114 0.561 ↑ 2.9 68 3

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.173..0.187 rows=68 loops=3)

  • Group Key: list_suspicious_aml_indicators_1.id
43. 0.447 0.447 ↑ 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.432..0.447 rows=194 loops=1)

44. 0.030 0.546 ↓ 2.0 2 3

Hash (cost=24.83..24.83 rows=1 width=8) (actual time=0.181..0.182 rows=2 loops=3)

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

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

  • Filter: (deal_aml_id = deal_state.last_deal_aml_id)
  • Rows Removed by Filter: 1280
46. 42.967 66.348 ↑ 1.3 16,020 3

HashAggregate (cost=17,908.89..18,165.45 rows=20,525 width=36) (actual time=12.132..22.116 rows=16,020 loops=3)

  • Group Key: payment_1.deal_id
47. 23.381 23.381 ↑ 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.082..23.381 rows=19,681 loops=1)

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

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

  • Group Key: "*SELECT* 1".customer_id
49. 0.015 104.319 ↓ 0.0 0 3

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

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

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

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

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

52. 0.002 66.204 ↑ 1.0 1 1

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

53. 0.013 66.202 ↑ 1.0 1 1

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

  • Group Key: payment_2.customer_id, payment_2.deal_id, payment_2.type
54. 0.014 66.189 ↑ 1.0 1 1

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

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

Bitmap Heap Scan on payment payment_2 (cost=2,913.04..22,430.34 rows=1 width=18) (actual time=66.174..66.175 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-07 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.299 13.299 ↑ 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.299..13.299 rows=134,261 loops=1)

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

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

58. 4.749 38.047 ↓ 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.047..38.047 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-07 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.257 33.298 ↓ 0.0 0 1

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

60. 3.293 3.293 ↑ 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.293..3.293 rows=37,132 loops=1)

  • Index Cond: (deal_id IS NULL)
61. 9.191 9.191 ↑ 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.191..9.191 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
62. 20.557 20.557 ↑ 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.557..20.557 rows=189,003 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.024 0.024 ↓ 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.019..0.024 rows=5 loops=1)

  • Index Cond: (agent_id = 4)
65. 0.003 85.122 ↓ 0.0 0 3

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

  • Group Key: "*SELECT* 1_1".customer_id
66. 0.013 85.119 ↓ 0.0 0 3

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

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

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

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

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

69. 0.001 55.753 ↓ 0.0 0 1

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

70. 0.002 55.752 ↓ 0.0 0 1

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

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

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

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

Bitmap Heap Scan on payment payment_4 (cost=2,913.04..22,767.88 rows=1 width=18) (actual time=55.742..55.743 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-07 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.408 9.408 ↑ 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.408..9.408 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
74. 0.002 29.348 ↓ 0.0 0 1

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

75. 4.535 29.346 ↓ 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.346..29.346 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-07 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.273 24.811 ↓ 0.0 0 1

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

77. 2.243 2.243 ↑ 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.243..2.243 rows=37,132 loops=1)

  • Index Cond: (deal_id IS NULL)
78. 8.906 8.906 ↑ 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.906..8.906 rows=134,261 loops=1)

  • Index Cond: (agent_id = 4)
79. 13.389 13.389 ↑ 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.389..13.389 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)