explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kjjZ : Payment Tasks - Eligible for Invoicing

Settings
# exclusive inclusive rows x rows loops node
1. 32.022 7,249.078 ↑ 535.5 326 1

GroupAggregate (cost=822,034.08..846,039.79 rows=174,562 width=112) (actual time=7,012.333..7,249.078 rows=326 loops=1)

  • Group Key: ecgps_core_payment.study_id, ecgps_core_study.study_name, ecgps_core_study.protocol, ecgps_core_study.slug
2. 1,141.334 7,217.056 ↑ 16.8 88,545 1

Sort (cost=822,034.08..825,744.09 rows=1,484,006 width=112) (actual time=7,012.322..7,217.056 rows=88,545 loops=1)

  • Sort Key: ecgps_core_payment.study_id, ecgps_core_study.study_name, ecgps_core_study.protocol, ecgps_core_study.slug
  • Sort Method: external merge Disk: 10,560kB
3. 46.004 6,075.722 ↑ 16.8 88,545 1

Hash Join (cost=135,505.82..497,456.56 rows=1,484,006 width=112) (actual time=1,264.315..6,075.722 rows=88,545 loops=1)

4. 4,792.845 6,029.224 ↑ 16.8 88,545 1

Seq Scan on ecgps_core_payment ecgps_core_payment (cost=135,408.85..476,954.5 rows=1,484,006 width=66) (actual time=1,263.816..6,029.224 rows=88,545 loops=1)

  • Filter: ((hashed SubPlan 2) OR ((hashed SubPlan 3) AND ((((status)::text = 'approved'::text) AND (payment_list_eligible_date IS NOT NULL) AND (NOT is_historic) AND (NOT is_reversal) AND ((invoice_number)::text = ''::text)) OR (((status)::text = 'released'::text) AND (NOT exempt_from_holdback) AND (amount_released > 0::numeric) AND (released_date IS NOT NULL) AND ((holdback_invoice_number)::text = ''::text) AND (holdback_eligible_date < '2020-06-30'::date) AND (amount_released < amount) AND (holdback_eligible_date IS NOT NULL)))))
5.          

SubPlan (for Seq Scan)

6. 13.058 1,203.099 ↓ 2.3 18,520 1

Nested Loop (cost=1,539.02..134,925.88 rows=8,006 width=33) (actual time=80.087..1,203.099 rows=18,520 loops=1)

7. 476.696 1,002.011 ↓ 2.3 18,803 1

Hash Join (cost=1,538.46..127,159.52 rows=8,134 width=33) (actual time=80.055..1,002.011 rows=18,803 loops=1)

8. 479.418 479.418 ↓ 1.0 2,905,215 1

Seq Scan on ecgps_core_invoicepaymentrecord w0 (cost=0..114,646.25 rows=2,904,925 width=66) (actual time=0.005..479.418 rows=2,905,215 loops=1)

9. 0.335 45.897 ↓ 1.9 1,124 1

Hash (cost=1,531.2..1,531.2 rows=581 width=33) (actual time=45.897..45.897 rows=1,124 loops=1)

10. 16.302 45.562 ↓ 1.9 1,124 1

Index Scan using idx_invoice_invoicestatus on ecgps_core_invoice w1 (cost=463.38..1,531.2 rows=581 width=33) (actual time=41.223..45.562 rows=1,124 loops=1)

  • Index Cond: ((invoice_status)::text = 'declined'::text)
  • Filter: (((NOT invoice_holdback) OR invoice_holdback) AND (hashed SubPlan 1))
11.          

SubPlan (for Index Scan)

12. 3.579 29.260 ↓ 54.6 30,982 1

Nested Loop (cost=5.52..461.54 rows=567 width=33) (actual time=0.213..29.26 rows=30,982 loops=1)

13. 0.290 5.091 ↓ 54.6 710 1

Nested Loop (cost=5.1..150.48 rows=13 width=66) (actual time=0.198..5.091 rows=710 loops=1)

14. 0.755 3.381 ↓ 54.6 710 1

Nested Loop (cost=4.83..142.86 rows=13 width=33) (actual time=0.192..3.381 rows=710 loops=1)

15. 0.343 0.496 ↓ 54.6 710 1

Bitmap Heap Scan on ecgps_core_userprofilestudy u0 (cost=4.55..54.89 rows=13 width=33) (actual time=0.184..0.496 rows=710 loops=1)

  • Heap Blocks: exact=233
16. 0.153 0.153 ↓ 54.6 710 1

Bitmap Index Scan on idx_userprofilestudy_profileid_sponsorportalaccess (cost=0..4.55 rows=13 width=0) (actual time=0.153..0.153 rows=710 loops=1)

  • Index Cond: (((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::text) AND ((sponsor_portal_access)::text = 'active'::text))
17. 2.130 2.130 ↑ 1.0 1 710

Index Scan using ecgps_core_study_pkey on ecgps_core_study v2 (cost=0.28..6.76 rows=1 width=66) (actual time=0.003..0.003 rows=1 loops=710)

  • Index Cond: ((id)::text = (u0.study_id)::text)
18. 1.420 1.420 ↑ 1.0 1 710

Index Only Scan using ecgps_core_event_id_391e7f0ea35b6e5f_like on ecgps_core_event v1 (cost=0.28..0.58 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=710)

  • Index Cond: (id = (v2.event_id)::text)
  • Heap Fetches: 710
19. 20.590 20.590 ↑ 1.1 44 710

Index Scan using ecgps_core_payeeevent_4437cfac on ecgps_core_payeeevent v0 (cost=0.41..23.44 rows=49 width=66) (actual time=0.007..0.029 rows=44 loops=710)

  • Index Cond: ((event_id)::text = (v1.id)::text)
20. 188.030 188.030 ↑ 1.0 1 18,803

Index Scan using ecgps_core_payment_pkey on ecgps_core_payment w3 (cost=0.56..0.94 rows=1 width=33) (actual time=0.01..0.01 rows=1 loops=18,803)

  • Index Cond: ((id)::text = (w0.payment_record_id)::text)
  • Filter: ((status)::text <> 'declined'::text)
21. 3.749 33.280 ↓ 54.6 30,982 1

Nested Loop (cost=5.52..461.54 rows=567 width=33) (actual time=0.257..33.28 rows=30,982 loops=1)

22. 0.411 6.101 ↓ 54.6 710 1

Nested Loop (cost=5.1..150.48 rows=13 width=66) (actual time=0.239..6.101 rows=710 loops=1)

23. 0.900 4.270 ↓ 54.6 710 1

Nested Loop (cost=4.83..142.86 rows=13 width=33) (actual time=0.229..4.27 rows=710 loops=1)

24. 1.067 1.240 ↓ 54.6 710 1

Bitmap Heap Scan on ecgps_core_userprofilestudy u0_1 (cost=4.55..54.89 rows=13 width=33) (actual time=0.218..1.24 rows=710 loops=1)

  • Heap Blocks: exact=233
25. 0.173 0.173 ↓ 54.6 710 1

Bitmap Index Scan on idx_userprofilestudy_profileid_sponsorportalaccess (cost=0..4.55 rows=13 width=0) (actual time=0.173..0.173 rows=710 loops=1)

  • Index Cond: (((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::text) AND ((sponsor_portal_access)::text = 'active'::text))
26. 2.130 2.130 ↑ 1.0 1 710

Index Scan using ecgps_core_study_pkey on ecgps_core_study v2_1 (cost=0.28..6.76 rows=1 width=66) (actual time=0.003..0.003 rows=1 loops=710)

  • Index Cond: ((id)::text = (u0_1.study_id)::text)
27. 1.420 1.420 ↑ 1.0 1 710

Index Only Scan using ecgps_core_event_id_391e7f0ea35b6e5f_like on ecgps_core_event v1_1 (cost=0.28..0.58 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=710)

  • Index Cond: (id = (v2_1.event_id)::text)
  • Heap Fetches: 710
28. 23.430 23.430 ↑ 1.1 44 710

Index Scan using ecgps_core_payeeevent_4437cfac on ecgps_core_payeeevent v0_1 (cost=0.41..23.44 rows=49 width=66) (actual time=0.007..0.033 rows=44 loops=710)

  • Index Cond: ((event_id)::text = (v1_1.id)::text)
29. 0.213 0.494 ↑ 1.0 710 1

Hash (cost=88.1..88.1 rows=710 width=79) (actual time=0.494..0.494 rows=710 loops=1)

30. 0.281 0.281 ↑ 1.0 710 1

Seq Scan on ecgps_core_study ecgps_core_study (cost=0..88.1 rows=710 width=79) (actual time=0.004..0.281 rows=710 loops=1)

Planning time : 4.77 ms
Execution time : 7,250.961 ms