explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zmMm

Settings
# exclusive inclusive rows x rows loops node
1. 1,733,415.565 1,733,415.565 ↑ 275,648.7 2,045 1

CTE Scan on queue_data (cost=839,543,931,127,820,582,912.00..839,543,931,127,831,855,104.00 rows=563,701,616 width=477) (actual time=1,733,323.455..1,733,415.565 rows=2,045 loops=1)

2.          

CTE queue_pop

3. 1.199 1,170,401.212 ↑ 81.6 2,045 1

GroupAggregate (cost=765,509,151,568,959,373,312.00..839,543,931,127,768,547,328.00 rows=166,899 width=68) (actual time=1,170,399.928..1,170,401.212 rows=2,045 loops=1)

  • Group Key: clients.prod_user_id
4. 0.671 1,170,400.013 ↑ 464,182,448,094,353,792.0 2,058 1

Sort (cost=765,509,151,568,959,373,312.00..767,897,370,264,404,885,504.00 rows=955,287,478,178,180,235,264 width=92) (actual time=1,170,399.914..1,170,400.013 rows=2,058 loops=1)

  • Sort Key: clients.prod_user_id
  • Sort Method: quicksort Memory: 253kB
5. 15.204 1,170,399.342 ↑ 464,182,448,094,353,792.0 2,058 1

Merge Left Join (cost=5,196,246.23..40,800,621,145,339,363,328.00 rows=955,287,478,178,180,235,264 width=92) (actual time=1,169,863.584..1,170,399.342 rows=2,058 loops=1)

  • Merge Cond: ((clients.prod_user_id)::numeric = ((draws.prod_user_id)::numeric))
  • Filter: (COALESCE(((CASE WHEN (business_request_notes.request_type ~~ '%INCREASE'::text) THEN 1 ELSE NULL::integer END))::bigint, notes.all_notes_to_risk, notes.user_notes, ((CASE WHEN (business_request_notes.request_type = 'PRIMARY_BANK_CHANGE'::text) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN (business_request_notes.request_type ~~ '%FLEX%'::text) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN ((business_request_notes.request_type = 'CLIENT_PRE_APPROVAL'::text) AND (business_request_notes.requested_credit_line > '0'::numeric)) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN (business_request_notes.request_type = 'TERM_LOAN_RENEWAL'::text) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN (business_request_notes.request_type = 'NEW_BANKING_APPLICATION'::text) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN (business_request_notes.request_type ~~ '%SBA%'::text) THEN 1 ELSE NULL::integer END))::bigint, ((CASE WHEN (a.changes_requested > 0) THEN 1 ELSE NULL::integer END))::bigint, advances.deal_ct, draws.draw_ct, files.has_vc, tasks.has_tasks) IS NOT NULL)
  • Rows Removed by Filter: 52,058
6. 147.503 1,119,816.805 ↑ 2,529,051,312,141,372.5 54,116 1

Merge Left Join (cost=4,897,766.47..1,370,638,378,599,648,000.00 rows=136,862,140,807,842,512,896 width=84) (actual time=1,119,296.110..1,119,816.805 rows=54,116 loops=1)

  • Merge Cond: ((clients.prod_user_id)::numeric = files.prod_user_id)
7. 67.263 1,081,665.040 ↑ 1,490,056,951,037.3 54,116 1

Merge Left Join (cost=3,042,066.05..1,613,790,909,552,895.00 rows=80,635,921,962,335,440 width=68) (actual time=1,081,462.039..1,081,665.040 rows=54,116 loops=1)

  • Merge Cond: ((clients.prod_user_id)::numeric = ((tasks.prod_user_id)::numeric))
8. 55.124 1,040,525.591 ↑ 720,250,072.4 54,116 1

Merge Left Join (cost=2,851,588.23..780,142,219,072.47 rows=38,977,052,918,145 width=60) (actual time=1,040,410.563..1,040,525.591 rows=54,116 loops=1)

  • Merge Cond: ((clients.prod_user_id)::numeric = ((offers.prod_user_id)::numeric))
9. 22.064 1,014,265.856 ↑ 401,735.8 54,116 1

Merge Left Join (cost=2,718,691.01..437,975,289.66 rows=21,740,336,124 width=60) (actual time=1,014,220.342..1,014,265.856 rows=54,116 loops=1)

  • Merge Cond: ((clients.prod_user_id)::numeric = ((business_request_notes.prod_user_id)::numeric))
10. 13.743 984,105.189 ↑ 296.7 54,103 1

Merge Left Join (cost=2,342,791.01..2,672,267.09 rows=16,054,303 width=32) (actual time=984,084.649..984,105.189 rows=54,103 loops=1)

  • Merge Cond: (((clients.prod_user_id)::numeric) = ((advances.prod_user_id)::numeric))
11. 55.431 906,911.229 ↑ 15.5 54,103 1

Sort (cost=2,252,342.07..2,254,439.57 rows=839,002 width=24) (actual time=906,904.470..906,911.229 rows=54,103 loops=1)

  • Sort Key: ((clients.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 4,121kB
12. 39.551 906,855.798 ↑ 15.5 54,103 1

Hash Left Join (cost=1,490,210.20..2,152,581.84 rows=839,002 width=24) (actual time=578,782.730..906,855.798 rows=54,103 loops=1)

  • Hash Cond: (clients.prod_user_id = notes.prod_user_id)
13. 43.684 887,228.280 ↑ 8.2 54,103 1

Hash Left Join (cost=842,342.33..1,503,544.08 rows=445,662 width=8) (actual time=559,191.988..887,228.280 rows=54,103 loops=1)

  • Hash Cond: ((clients.prod_user_id)::numeric = a.prod_user_id)
14. 327,992.663 327,992.663 ↑ 8.2 54,103 1

Index Scan using clients_last_record_idx on clients (cost=0.56..659,949.45 rows=445,662 width=4) (actual time=0.037..327,992.663 rows=54,103 loops=1)

  • Index Cond: (last_record = '1'::numeric)
  • Filter: (is_real AND (NOT is_test) AND ((user_account_type)::text !~~ '%ppp%'::text) AND (user_status <> ALL ('{CREATED,UNQUALIFIED,CANCELED,REJECTED,INACTIVE}'::text[])))
  • Rows Removed by Filter: 1,403,455
15. 0.012 559,191.933 ↑ 3.1 19 1

Hash (cost=842,341.04..842,341.04 rows=58 width=12) (actual time=559,191.933..559,191.933 rows=19 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.006 559,191.921 ↑ 3.1 19 1

Subquery Scan on a (cost=842,339.30..842,341.04 rows=58 width=12) (actual time=559,191.898..559,191.921 rows=19 loops=1)

17. 0.026 559,191.915 ↑ 3.1 19 1

GroupAggregate (cost=842,339.30..842,340.31 rows=58 width=16) (actual time=559,191.896..559,191.915 rows=19 loops=1)

  • Group Key: events_change_request_04102020_11102020.prod_user_id
18. 0.081 559,191.889 ↑ 2.5 23 1

Sort (cost=842,339.30..842,339.44 rows=58 width=8) (actual time=559,191.887..559,191.889 rows=23 loops=1)

  • Sort Key: events_change_request_04102020_11102020.prod_user_id
  • Sort Method: quicksort Memory: 26kB
19. 0.450 559,191.808 ↑ 2.5 23 1

Hash Join (cost=667,850.49..842,337.60 rows=58 width=8) (actual time=538,117.999..559,191.808 rows=23 loops=1)

  • Hash Cond: (events_change_request_04102020_11102020.prod_user_id = (clients_1.prod_user_id)::numeric)
20. 0.057 27,803.414 ↑ 2.0 57 1

Append (cost=0.57..174,486.65 rows=116 width=8) (actual time=4,178.919..27,803.414 rows=57 loops=1)

  • Subplans Removed: 102
21. 26,159.843 26,627.740 ↓ 28.0 56 1

Bitmap Heap Scan on events_change_request_04102020_11102020 (cost=1,994.94..166,103.66 rows=2 width=5) (actual time=4,178.918..26,627.740 rows=56 loops=1)

  • Recheck Cond: (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[]))
  • Filter: ((last_record = 1) AND ((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)))
  • Rows Removed by Filter: 71,585
  • Heap Blocks: exact=34,253
22. 467.897 467.897 ↓ 1.1 71,693 1

Bitmap Index Scan on events_change_request_field_name_idx_04102020 (cost=0.00..1,994.93 rows=66,959 width=0) (actual time=467.897..467.897 rows=71,693 loops=1)

  • Index Cond: (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[]))
23. 1,167.303 1,175.566 ↑ 1.0 1 1

Bitmap Heap Scan on events_change_request_11102020_18102020 (cost=112.53..7,808.17 rows=1 width=5) (actual time=102.705..1,175.566 rows=1 loops=1)

  • Recheck Cond: (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[]))
  • Filter: ((last_record = 1) AND ((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)))
  • Rows Removed by Filter: 2,734
  • Heap Blocks: exact=1,329
24. 8.263 8.263 ↓ 1.0 2,757 1

Bitmap Index Scan on events_change_request_field_name_idx_11102020 (cost=0.00..112.53 rows=2,653 width=0) (actual time=8.263..8.263 rows=2,757 loops=1)

  • Index Cond: (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[]))
25. 0.008 0.008 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_18102020 on events_change_request_18102020_25102020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
26. 0.004 0.004 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_25102020 on events_change_request_25102020_01112020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
27. 0.008 0.008 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_01112020 on events_change_request_01112020_08112020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
28. 0.003 0.003 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_08112020 on events_change_request_08112020_15112020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
29. 0.004 0.004 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_15112020 on events_change_request_15112020_22112020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
30. 0.006 0.006 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_22112020 on events_change_request_22112020_29112020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
31. 0.004 0.004 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_29112020 on events_change_request_29112020_06122020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
32. 0.003 0.003 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_06122020 on events_change_request_06122020_13122020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
33. 0.003 0.003 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_13122020 on events_change_request_13122020_20122020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
34. 0.005 0.005 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_20122020 on events_change_request_20122020_27122020 (cost=0.14..9.75 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
35. 0.003 0.003 ↓ 0.0 0 1

Index Scan using events_change_request_row_id_last_record_idx_27122020 on events_change_request_27122020_03012021 (cost=0.14..9.75 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (last_record = 1)
  • Filter: (((status)::text = 'P'::text) AND (updated > (now() - '7 days'::interval)) AND (field_name = ANY ('{email,personal_user_first_name,personal_user_last_name,business_entity_type,personal_authorised_person_full_name,bluevine_due_date,number_of_installments,business_address,business_address_state,business_address_zip,business_name}'::text[])))
36. 60.523 531,387.944 ↓ 1.1 189,678 1

Hash (cost=665,706.97..665,706.97 rows=171,436 width=4) (actual time=531,387.944..531,387.944 rows=189,678 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,717kB
37. 269.615 531,327.421 ↓ 1.1 189,678 1

HashAggregate (cost=663,992.61..665,706.97 rows=171,436 width=4) (actual time=531,276.512..531,327.421 rows=189,678 loops=1)

  • Group Key: (clients_1.prod_user_id)::numeric
38. 531,057.806 531,057.806 ↑ 2.7 189,678 1

Index Scan using clients_last_record_idx on clients clients_1 (cost=0.56..662,724.77 rows=507,137 width=4) (actual time=0.057..531,057.806 rows=189,678 loops=1)

  • Index Cond: (last_record = '1'::numeric)
  • Filter: (is_real AND (flags !~~ '%wait%'::text) AND (user_status = ANY ('{APPLIED,APPROVED,SUSPENDED}'::text[])))
  • Rows Removed by Filter: 1,267,880
39. 6.016 19,587.967 ↑ 17.9 19,185 1

Hash (cost=643,569.60..643,569.60 rows=343,862 width=20) (actual time=19,587.967..19,587.967 rows=19,185 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 4,969kB
40. 2.334 19,581.951 ↑ 17.9 19,185 1

Subquery Scan on notes (cost=636,692.36..643,569.60 rows=343,862 width=20) (actual time=19,571.377..19,581.951 rows=19,185 loops=1)

41. 32.853 19,579.617 ↑ 17.9 19,185 1

HashAggregate (cost=636,692.36..640,130.98 rows=343,862 width=20) (actual time=19,571.376..19,579.617 rows=19,185 loops=1)

  • Group Key: a_1.prod_user_id
42. 19,546.764 19,546.764 ↑ 25.5 27,391 1

Seq Scan on unified_notes a_1 (cost=0.00..629,694.48 rows=699,788 width=8) (actual time=41.593..19,546.764 rows=27,391 loops=1)

  • Filter: ((NOT deleted) AND (prod_user_id IS NOT NULL) AND ((target_group)::text = 'risk'::text) AND (last_record = 1) AND (((status)::text = 'open'::text) OR ((status)::text = 'in process'::text)))
  • Rows Removed by Filter: 5,858,299
43. 2.884 77,180.217 ↑ 8.1 472 1

Sort (cost=90,448.94..90,458.51 rows=3,827 width=12) (actual time=77,180.170..77,180.217 rows=472 loops=1)

  • Sort Key: ((advances.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 61kB
44. 0.070 77,177.333 ↑ 8.1 472 1

Subquery Scan on advances (cost=90,055.26..90,221.20 rows=3,827 width=12) (actual time=77,177.051..77,177.333 rows=472 loops=1)

45. 0.157 77,177.263 ↑ 8.1 472 1

GroupAggregate (cost=90,055.26..90,182.93 rows=3,827 width=12) (actual time=77,177.048..77,177.263 rows=472 loops=1)

  • Group Key: advances_1.prod_user_id
46. 0.830 77,177.106 ↑ 11.4 1,043 1

Sort (cost=90,055.26..90,085.06 rows=11,920 width=4) (actual time=77,177.039..77,177.106 rows=1,043 loops=1)

  • Sort Key: advances_1.prod_user_id
  • Sort Method: quicksort Memory: 97kB
47. 77,176.276 77,176.276 ↑ 11.4 1,043 1

Index Scan using advances_last_record_idx on advances advances_1 (cost=0.42..89,248.21 rows=11,920 width=4) (actual time=255.413..77,176.276 rows=1,043 loops=1)

  • Filter: (((waiting_for)::text = 'R'::text) AND ((now() - '00:50:00'::interval) > deal_update_date))
  • Rows Removed by Filter: 361,100
48. 39.626 30,138.603 ↑ 10.2 26,447 1

Sort (cost=375,900.00..376,577.08 rows=270,835 width=32) (actual time=30,135.685..30,138.603 rows=26,447 loops=1)

  • Sort Key: ((business_request_notes.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 2,835kB
49. 19,262.460 30,098.977 ↑ 10.2 26,447 1

Bitmap Heap Scan on business_request_notes (cost=104,904.05..351,461.13 rows=270,835 width=32) (actual time=10,843.462..30,098.977 rows=26,447 loops=1)

  • Recheck Cond: ((status = 'PENDING'::text) AND (last_record = 1))
  • Filter: (((waiting_for)::text = 'R'::text) AND (((request_type ~~ '%INCREASE'::text) AND (last_updated < (now() - '00:30:00'::interval))) OR (request_type !~~ '%INCREASE'::text)))
  • Rows Removed by Filter: 18,083
  • Heap Blocks: exact=33,924
50. 10,836.517 10,836.517 ↑ 11.3 74,318 1

Bitmap Index Scan on idx_business_request_notes_last_record_request_type_status (cost=0.00..104,836.34 rows=836,546 width=0) (actual time=10,836.517..10,836.517 rows=74,318 loops=1)

  • Index Cond: (status = 'PENDING'::text)
51. 117.689 26,204.611 ↑ 1.4 261,899 1

Sort (cost=132,897.22..133,793.65 rows=358,569 width=4) (actual time=26,190.213..26,204.611 rows=261,899 loops=1)

  • Sort Key: ((offers.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 18,421kB
52. 74.417 26,086.922 ↑ 1.4 261,899 1

Subquery Scan on offers (cost=0.42..99,815.84 rows=358,569 width=4) (actual time=15.237..26,086.922 rows=261,899 loops=1)

53. 254.446 26,012.505 ↑ 1.4 261,899 1

GroupAggregate (cost=0.42..96,230.15 rows=358,569 width=20) (actual time=15.234..26,012.505 rows=261,899 loops=1)

  • Group Key: creditline_offers.user_id
54. 25,758.059 25,758.059 ↑ 1.8 312,160 1

Index Scan using creditline_offers_user_id_idx on creditline_offers (cost=0.42..91,246.03 rows=559,371 width=4) (actual time=13.233..25,758.059 rows=312,160 loops=1)

  • Filter: (status = ANY ('{EXPIRED_SALES,EXPIRED_CLIENT,PENDING_SALES,PENDING_CLIENT,ACCEPTED}'::text[]))
  • Rows Removed by Filter: 507,519
55. 178.839 41,072.186 ↑ 1.1 385,209 1

Sort (cost=190,477.82..191,512.22 rows=413,761 width=12) (actual time=41,051.468..41,072.186 rows=385,209 loops=1)

  • Sort Key: ((tasks.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 39,169kB
56. 106.133 40,893.347 ↑ 1.1 385,204 1

Subquery Scan on tasks (cost=0.43..151,877.15 rows=413,761 width=12) (actual time=602.594..40,893.347 rows=385,204 loops=1)

57. 313.028 40,787.214 ↑ 1.1 385,204 1

GroupAggregate (cost=0.43..147,739.54 rows=413,761 width=12) (actual time=602.590..40,787.214 rows=385,204 loops=1)

  • Group Key: user_tasks.user_id
58. 40,474.186 40,474.186 ↑ 1.0 717,510 1

Index Scan using user_tasks_user_id on user_tasks (cost=0.43..139,962.54 rows=727,878 width=4) (actual time=437.733..40,474.186 rows=717,510 loops=1)

  • Filter: ((status)::text = 'PENDING_REVIEW'::text)
  • Rows Removed by Filter: 1,065,838
59. 716.345 38,004.262 ↓ 2.8 945,563 1

Sort (cost=1,855,700.42..1,856,549.07 rows=339,457 width=22) (actual time=37,834.060..38,004.262 rows=945,563 loops=1)

  • Sort Key: files.prod_user_id
  • Sort Method: external merge Disk: 16,776kB
60. 91.105 37,287.917 ↓ 2.8 945,563 1

Subquery Scan on files (cost=1,817,727.29..1,824,516.43 rows=339,457 width=22) (actual time=36,945.911..37,287.917 rows=945,563 loops=1)

61. 3,085.057 37,196.812 ↓ 2.8 945,563 1

HashAggregate (cost=1,817,727.29..1,821,121.86 rows=339,457 width=22) (actual time=36,945.909..37,196.812 rows=945,563 loops=1)

  • Group Key: files_1.prod_user_id
62. 34,111.755 34,111.755 ↓ 1.0 4,793,922 1

Seq Scan on files files_1 (cost=0.00..1,638,096.02 rows=4,790,167 width=37) (actual time=0.387..34,111.755 rows=4,793,922 loops=1)

  • Filter: (last_record = 1)
  • Rows Removed by Filter: 5,692,057
63. 0.072 50,567.333 ↑ 3.4 416 1

Materialize (cost=298,479.76..298,486.77 rows=1,403 width=12) (actual time=50,567.222..50,567.333 rows=416 loops=1)

64. 0.132 50,567.261 ↑ 3.4 416 1

Sort (cost=298,479.76..298,483.26 rows=1,403 width=12) (actual time=50,567.216..50,567.261 rows=416 loops=1)

  • Sort Key: ((draws.prod_user_id)::numeric)
  • Sort Method: quicksort Memory: 57kB
65. 0.064 50,567.129 ↑ 3.4 416 1

Subquery Scan on draws (cost=298,367.43..298,406.42 rows=1,403 width=12) (actual time=50,566.846..50,567.129 rows=416 loops=1)

66. 0.156 50,567.065 ↑ 3.4 416 1

GroupAggregate (cost=298,367.43..298,392.39 rows=1,403 width=12) (actual time=50,566.844..50,567.065 rows=416 loops=1)

  • Group Key: draws_1.prod_user_id
67. 0.930 50,566.909 ↑ 1.3 1,095 1

Sort (cost=298,367.43..298,371.07 rows=1,458 width=4) (actual time=50,566.836..50,566.909 rows=1,095 loops=1)

  • Sort Key: draws_1.prod_user_id
  • Sort Method: quicksort Memory: 100kB
68. 50,565.979 50,565.979 ↑ 1.3 1,095 1

Index Scan using draws_special_draw_deal_status_idx on draws draws_1 (cost=0.42..298,290.81 rows=1,458 width=4) (actual time=38,969.178..50,565.979 rows=1,095 loops=1)

  • Filter: (((waiting_for)::text = 'R'::text) AND ((now() - '00:50:00'::interval) > deal_update_date))
  • Rows Removed by Filter: 155,711
69.          

CTE queue_data

70. 61.604 1,733,413.875 ↑ 275,648.7 2,045 1

Hash Left Join (cost=6,138,937.78..51,979,332.81 rows=563,701,616 width=323) (actual time=1,733,323.451..1,733,413.875 rows=2,045 loops=1)

  • Hash Cond: (queue.prod_user_id = current_variables.prod_user_id)
71. 9.369 1,540,936.085 ↑ 200.1 2,045 1

Merge Join (cost=747,063.47..758,370.01 rows=409,301 width=189) (actual time=1,540,901.425..1,540,936.085 rows=2,045 loops=1)

  • Merge Cond: (queue.prod_user_id = clients_2.prod_user_id)
72. 0.731 1,177,802.737 ↑ 81.6 2,045 1

Merge Left Join (cost=20,830.76..24,267.52 rows=166,899 width=88) (actual time=1,177,797.504..1,177,802.737 rows=2,045 loops=1)

  • Merge Cond: (queue.prod_user_id = current_queue_status.prod_user_id)
73. 2.210 1,170,528.005 ↑ 81.6 2,045 1

Merge Left Join (cost=20,251.38..23,249.52 rows=166,899 width=80) (actual time=1,170,523.787..1,170,528.005 rows=2,045 loops=1)

  • Merge Cond: (queue.prod_user_id = dda_bank_account.prod_user_id)
74. 0.453 1,170,402.307 ↑ 81.6 2,045 1

Sort (cost=17,815.31..18,232.56 rows=166,899 width=68) (actual time=1,170,402.075..1,170,402.307 rows=2,045 loops=1)

  • Sort Key: queue.prod_user_id
  • Sort Method: quicksort Memory: 208kB
75. 1,170,401.854 1,170,401.854 ↑ 81.6 2,045 1

CTE Scan on queue_pop queue (cost=0.00..3,337.98 rows=166,899 width=68) (actual time=1,170,399.930..1,170,401.854 rows=2,045 loops=1)

76. 5.791 123.488 ↓ 1.2 15,334 1

Sort (cost=2,436.07..2,467.44 rows=12,549 width=16) (actual time=121.705..123.488 rows=15,334 loops=1)

  • Sort Key: dda_bank_account.prod_user_id
  • Sort Method: quicksort Memory: 1,260kB
77. 117.697 117.697 ↓ 1.2 15,334 1

Index Scan using dda_bank_account_last_record on dda_bank_account (cost=0.43..1,581.78 rows=12,549 width=16) (actual time=0.301..117.697 rows=15,334 loops=1)

  • Index Cond: (last_record = 1)
78. 0.747 7,274.001 ↓ 1.1 2,014 1

Sort (cost=579.38..583.78 rows=1,763 width=12) (actual time=7,273.710..7,274.001 rows=2,014 loops=1)

  • Sort Key: current_queue_status.prod_user_id
  • Sort Method: quicksort Memory: 143kB
79. 7,273.254 7,273.254 ↓ 1.1 2,014 1

Index Scan using current_queue_status_is_dismissed on current_queue_status (cost=0.43..484.32 rows=1,763 width=12) (actual time=0.851..7,273.254 rows=2,014 loops=1)

  • Index Cond: (is_dismissed = false)
  • Filter: (NOT is_dismissed)
80. 11.564 363,123.979 ↑ 7.4 60,403 1

Materialize (cost=726,232.72..728,472.40 rows=447,936 width=105) (actual time=363,103.841..363,123.979 rows=60,403 loops=1)

81. 95.776 363,112.415 ↑ 7.4 60,403 1

Sort (cost=726,232.72..727,352.56 rows=447,936 width=105) (actual time=363,103.837..363,112.415 rows=60,403 loops=1)

  • Sort Key: clients_2.prod_user_id
  • Sort Method: quicksort Memory: 16,943kB
82. 363,016.639 363,016.639 ↑ 7.4 60,404 1

Index Scan using clients_last_record_idx on clients clients_2 (cost=0.56..658,157.85 rows=447,936 width=105) (actual time=6.482..363,016.639 rows=60,404 loops=1)

  • Index Cond: (last_record = '1'::numeric)
  • Filter: (is_real AND (NOT is_test) AND ((user_account_type)::text !~~ '%ppp%'::text) AND (user_status <> ALL ('{CREATED,UNQUALIFIED,CANCELED,REJECTED}'::text[])))
  • Rows Removed by Filter: 1,397,154
83. 266.222 192,416.186 ↓ 3.2 886,089 1

Hash (cost=5,382,782.23..5,382,782.23 rows=275,446 width=141) (actual time=192,416.186..192,416.186 rows=886,089 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 25,484kB
84. 120.178 192,149.964 ↓ 3.2 886,089 1

Subquery Scan on current_variables (cost=5,275,636.75..5,382,782.23 rows=275,446 width=141) (actual time=190,882.180..192,149.964 rows=886,089 loops=1)

85. 513.729 192,029.786 ↓ 3.2 886,089 1

Finalize GroupAggregate (cost=5,275,636.75..5,380,027.77 rows=275,446 width=141) (actual time=190,882.178..192,029.786 rows=886,089 loops=1)

  • Group Key: client_variable_vector.entity_id
86. 295.893 191,516.057 ↓ 2.3 1,290,625 1

Gather Merge (cost=5,275,636.75..5,367,632.70 rows=550,892 width=141) (actual time=190,882.157..191,516.057 rows=1,290,625 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
87. 295.031 191,220.164 ↓ 1.6 430,208 3 / 3

Partial GroupAggregate (cost=5,274,636.73..5,303,046.07 rows=275,446 width=141) (actual time=190,835.431..191,220.164 rows=430,208 loops=3)

  • Group Key: client_variable_vector.entity_id
88. 483.769 190,925.133 ↑ 1.2 513,797 3 / 3

Sort (cost=5,274,636.73..5,276,240.16 rows=641,372 width=51) (actual time=190,835.416..190,925.133 rows=513,797 loops=3)

  • Sort Key: client_variable_vector.entity_id
  • Sort Method: external merge Disk: 15,912kB
  • Worker 0: Sort Method: external merge Disk: 15,776kB
  • Worker 1: Sort Method: external merge Disk: 15,760kB
89. 190,441.364 190,441.364 ↑ 1.2 513,797 3 / 3

Parallel Index Scan using client_variable_vector_var_id_idx on client_variable_vector (cost=0.57..5,190,849.83 rows=641,372 width=51) (actual time=0.830..190,441.364 rows=513,797 loops=3)

  • Index Cond: (var_id = ANY ('{1321,1683,1292,802,803}'::integer[]))
Planning time : 56.536 ms
Execution time : 1,733,441.178 ms