explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xvQ4S : Query 1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 16.201 4,758.557 ↑ 8.4 146 1

HashAggregate (cost=8,849.21..8,861.43 rows=1,222 width=112) (actual time=4,758.528..4,758.557 rows=146 loops=1)

  • Group Key: ecgps_core_payment.study_id, ecgps_core_study.study_name, ecgps_core_study.protocol, ecgps_core_study.slug
2. 13.844 4,742.356 ↓ 6.4 23,069 1

Hash Left Join (cost=144.65..8,803.86 rows=3,628 width=112) (actual time=1.262..4,742.356 rows=23,069 loops=1)

  • Hash Cond: ((ecgps_core_payment.payment_creation_type_id)::text = (ecgps_core_paymentcreationtype.id)::text)
  • Filter: (((ecgps_core_paymentcreationtype.short_name)::text = 'standard'::text) OR (ecgps_core_payment.payment_creation_type_id IS NULL))
  • Rows Removed by Filter: 50
3. 7.873 4,728.506 ↓ 5.7 23,119 1

Nested Loop (cost=143.60..8,775.93 rows=4,090 width=145) (actual time=1.250..4,728.506 rows=23,119 loops=1)

4. 1.126 8.309 ↓ 92.3 554 1

Nested Loop (cost=4.76..81.06 rows=6 width=112) (actual time=0.110..8.309 rows=554 loops=1)

5. 1.000 1.089 ↓ 92.3 554 1

Bitmap Heap Scan on ecgps_core_userprofilestudy u0 (cost=4.49..39.23 rows=6 width=33) (actual time=0.104..1.089 rows=554 loops=1)

  • Recheck Cond: ((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::text)
  • Filter: ((sponsor_portal_access)::text = 'active'::text)
  • Heap Blocks: exact=77
6. 0.089 0.089 ↓ 61.6 554 1

Bitmap Index Scan on ecgps_core_userprofilestudy_profile_id_like (cost=0.00..4.49 rows=9 width=0) (actual time=0.089..0.089 rows=554 loops=1)

  • Index Cond: ((profile_id)::text = '3ef4d33492f3489985aaba7eeb5e3918'::text)
7. 6.094 6.094 ↑ 1.0 1 554

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

  • Index Cond: ((id)::text = (u0.study_id)::text)
8. 4,270.232 4,712.324 ↓ 1.2 42 554

Bitmap Heap Scan on ecgps_core_payment (cost=138.84..1,448.79 rows=35 width=99) (actual time=6.367..8.506 rows=42 loops=554)

  • Recheck Cond: ((study_id)::text = (ecgps_core_study.id)::text)
  • Rows Removed by Index Recheck: 1,208
  • Filter: ((NOT is_auto_approve) AND ((status)::text = 'pending'::text))
  • Rows Removed by Filter: 3,755
  • Heap Blocks: exact=909,407 lossy=79,179
9. 442.092 442.092 ↑ 1.5 3,801 554

Bitmap Index Scan on ecgps_core_payment_study_id_like (cost=0.00..138.83 rows=5,753 width=0) (actual time=0.798..0.798 rows=3,801 loops=554)

  • Index Cond: ((study_id)::text = (ecgps_core_study.id)::text)
10. 0.002 0.006 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=356) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
11. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on ecgps_core_paymentcreationtype (cost=0.00..1.02 rows=2 width=356) (actual time=0.004..0.004 rows=2 loops=1)

Planning time : 1.658 ms