explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWsu : Sharing - Payments Eligible

Settings
# exclusive inclusive rows x rows loops node
1. 34.391 7,444.728 ↑ 535.5 326 1

GroupAggregate (cost=842,846.48..866,852.19 rows=174,562 width=112) (actual time=7,191.017..7,444.728 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,186.935 7,410.337 ↑ 16.8 88,545 1

Sort (cost=842,846.48..846,556.5 rows=1,484,006 width=112) (actual time=7,191.005..7,410.337 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,568kB
3. 45.045 6,223.402 ↑ 16.8 88,545 1

Hash Join (cost=156,318.23..518,268.96 rows=1,484,006 width=112) (actual time=1,456.644..6,223.402 rows=88,545 loops=1)

4. 4,748.415 6,177.891 ↑ 16.8 88,545 1

Seq Scan on ecgps_core_payment ecgps_core_payment (cost=156,221.25..497,766.9 rows=1,484,006 width=66) (actual time=1,456.173..6,177.891 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-07-06'::date) AND (amount_released < amount) AND (holdback_eligible_date IS NOT NULL)))))
5.          

SubPlan (for Seq Scan)

6. 12.784 1,397.638 ↓ 2.3 18,520 1

Nested Loop (cost=22,307.26..155,694.13 rows=8,006 width=33) (actual time=296.352..1,397.638 rows=18,520 loops=1)

7. 472.777 1,196.824 ↓ 2.3 18,803 1

Hash Join (cost=22,306.71..147,927.77 rows=8,134 width=33) (actual time=296.315..1,196.824 rows=18,803 loops=1)

8. 462.428 462.428 ↓ 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..462.428 rows=2,905,215 loops=1)

9. 0.669 261.619 ↓ 1.9 1,124 1

Hash (cost=22,299.45..22,299.45 rows=581 width=33) (actual time=261.619..261.619 rows=1,124 loops=1)

10. 229.635 260.950 ↓ 1.9 1,124 1

Seq Scan on ecgps_core_invoice w1 (cost=507.11..22,299.45 rows=581 width=33) (actual time=45.187..260.95 rows=1,124 loops=1)

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

SubPlan (for Seq Scan)

12. 3.618 31.315 ↓ 51.2 30,982 1

Nested Loop (cost=5.53..505.6 rows=605 width=33) (actual time=0.168..31.315 rows=30,982 loops=1)

13. 0.479 5.687 ↓ 54.6 710 1

Nested Loop (cost=5.12..173.16 rows=13 width=66) (actual time=0.153..5.687 rows=710 loops=1)

14. 0.278 3.788 ↓ 54.6 710 1

Nested Loop (cost=4.84..165.54 rows=13 width=33) (actual time=0.137..3.788 rows=710 loops=1)

15. 0.573 0.670 ↓ 54.6 710 1

Bitmap Heap Scan on ecgps_core_userprofilestudy u0 (cost=4.57..77.57 rows=13 width=33) (actual time=0.13..0.67 rows=710 loops=1)

  • Filter: ((sponsor_portal_access)::text = 'active'::text)
  • Heap Blocks: exact=233
16. 0.097 0.097 ↓ 37.4 710 1

Bitmap Index Scan on ecgps_core_userprofilestudy_profile_id_like (cost=0..4.56 rows=19 width=0) (actual time=0.097..0.097 rows=710 loops=1)

  • Index Cond: ((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::text)
17. 2.840 2.840 ↑ 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.004 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. 22.010 22.010 ↑ 1.2 44 710

Index Scan using ecgps_core_payeeevent_4437cfac on ecgps_core_payeeevent v0 (cost=0.41..25.05 rows=52 width=66) (actual time=0.007..0.031 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.675 31.838 ↓ 51.2 30,982 1

Nested Loop (cost=5.53..505.6 rows=605 width=33) (actual time=0.242..31.838 rows=30,982 loops=1)

22. 0.349 6.153 ↓ 54.6 710 1

Nested Loop (cost=5.12..173.16 rows=13 width=66) (actual time=0.224..6.153 rows=710 loops=1)

23. 0.827 4.384 ↓ 54.6 710 1

Nested Loop (cost=4.84..165.54 rows=13 width=33) (actual time=0.213..4.384 rows=710 loops=1)

24. 1.275 1.427 ↓ 54.6 710 1

Bitmap Heap Scan on ecgps_core_userprofilestudy u0_1 (cost=4.57..77.57 rows=13 width=33) (actual time=0.202..1.427 rows=710 loops=1)

  • Filter: ((sponsor_portal_access)::text = 'active'::text)
  • Heap Blocks: exact=233
25. 0.152 0.152 ↓ 37.4 710 1

Bitmap Index Scan on ecgps_core_userprofilestudy_profile_id_like (cost=0..4.56 rows=19 width=0) (actual time=0.152..0.152 rows=710 loops=1)

  • Index Cond: ((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::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. 22.010 22.010 ↑ 1.2 44 710

Index Scan using ecgps_core_payeeevent_4437cfac on ecgps_core_payeeevent v0_1 (cost=0.41..25.05 rows=52 width=66) (actual time=0.007..0.031 rows=44 loops=710)

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

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

30. 0.264 0.264 ↑ 1.0 710 1

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

Planning time : 9.016 ms
Execution time : 7,446.68 ms