explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kMuw : Payments Declined

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 34.083 5,842.942 ↑ 12.6 435 1

HashAggregate (cost=20,745.68..20,800.42 rows=5,474 width=112) (actual time=5,842.865..5,842.942 rows=435 loops=1)

  • Group Key: ecgps_core_payment.study_id, ecgps_core_study.study_name, ecgps_core_study.protocol, ecgps_core_study.slug
2. 18.738 5,808.859 ↓ 3.5 41,835 1

Hash Join (cost=1,234.62..20,598.24 rows=11,795 width=112) (actual time=10.741..5,808.859 rows=41,835 loops=1)

  • Filter: (((ecgps_core_paymentcreationtype.short_name)::text = 'standard'::text) OR (ecgps_core_payment.payment_creation_type_id IS NULL))
3. 18.453 5,790.116 ↓ 2.8 41,929 1

Nested Loop (cost=1,233.58..20,454.85 rows=15,208 width=145) (actual time=10.724..5,790.116 rows=41,929 loops=1)

4. 2.188 12.853 ↓ 54.6 710 1

Nested Loop (cost=4.83..142.86 rows=13 width=112) (actual time=0.214..12.853 rows=710 loops=1)

5. 2.686 2.855 ↓ 54.6 710 1

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

  • Heap Blocks: exact=233
6. 0.169 0.169 ↓ 54.6 710 1

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

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

Index Scan using ecgps_core_study_pkey on ecgps_core_study ecgps_core_study (cost=0.28..6.76 rows=1 width=79) (actual time=0.009..0.011 rows=1 loops=710)

  • Index Cond: ((id)::text = (u0.study_id)::text)
8. 200.930 5,758.810 ↑ 1.6 59 710

Bitmap Heap Scan on ecgps_core_payment ecgps_core_payment (cost=1,228.75..1,561.51 rows=95 width=99) (actual time=7.847..8.111 rows=59 loops=710)

  • Heap Blocks: exact=31,465 lossy=13,707
9. 209.625 5,557.880 ↓ 0.0 0 710

BitmapAnd (cost=1,228.75..1,228.75 rows=95 width=0) (actual time=7.828..7.828 rows=0 loops=710)

10. 571.550 571.550 ↑ 1.4 4,166 710

Bitmap Index Scan on ecgps_core_payment_study_id_like (cost=0..147.41 rows=6,036 width=0) (actual time=0.805..0.805 rows=4,166 loops=710)

  • Index Cond: ((study_id)::text = (ecgps_core_study.id)::text)
11. 4,776.705 4,776.705 ↑ 1.1 41,929 615

Bitmap Index Scan on idx_payment_status_isautoapprove (cost=0..1,069.43 rows=46,533 width=0) (actual time=7.767..7.767 rows=41,929 loops=615)

  • Index Cond: ((status)::text = 'declined'::text)
12. 0.003 0.005 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=42) (actual time=0.005..0.005 rows=2 loops=1)

13. 0.002 0.002 ↑ 1.0 2 1

Seq Scan on ecgps_core_paymentcreationtype ecgps_core_paymentcreationtype (cost=0..1.02 rows=2 width=42) (actual time=0.002..0.002 rows=2 loops=1)

Planning time : 2.439 ms
Execution time : 5,843.086 ms