explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LXgb : Declined Payments - Single-Column Index

Settings
# exclusive inclusive rows x rows loops node
1. 29.825 6,501.799 ↑ 12.6 435 1

HashAggregate (cost=20,612.36..20,667.10 rows=5,474 width=112) (actual time=6,501.725..6,501.799 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. 19.769 6,471.974 ↓ 3.5 41,835 1

Hash Left Join (cost=1,222.63..20,464.92 rows=11,795 width=112) (actual time=9.581..6,471.974 rows=41,835 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: 94
3. 19.588 6,452.197 ↓ 2.8 41,929 1

Nested Loop (cost=1,221.59..20,321.53 rows=15,208 width=145) (actual time=9.553..6,452.197 rows=41,929 loops=1)

4. 2.549 14.209 ↓ 54.6 710 1

Nested Loop (cost=4.84..165.54 rows=13 width=112) (actual time=0.184..14.209 rows=710 loops=1)

5. 4.436 4.560 ↓ 54.6 710 1

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

  • Recheck Cond: ((profile_id)::text = 'e1a820195f7543158b2d71919eb4db55'::text)
  • Filter: ((sponsor_portal_access)::text = 'active'::text)
  • Heap Blocks: exact=338
6. 0.124 0.124 ↓ 37.4 710 1

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

  • Index Cond: ((profile_id)::text = 'e1a820195f7543158b2d71919eb4db55'::text)
7. 7.100 7.100 ↑ 1.0 1 710

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

  • Index Cond: ((id)::text = (u0.study_id)::text)
8. 200.220 6,418.400 ↑ 1.6 59 710

Bitmap Heap Scan on ecgps_core_payment (cost=1,216.75..1,549.51 rows=95 width=99) (actual time=8.777..9.040 rows=59 loops=710)

  • Recheck Cond: (((study_id)::text = (ecgps_core_study.id)::text) AND ((status)::text = 'declined'::text))
  • Rows Removed by Index Recheck: 231
  • Heap Blocks: exact=31,465 lossy=13,707
9. 266.975 6,218.180 ↓ 0.0 0 710

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

10. 596.400 596.400 ↑ 1.4 4,166 710

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

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

Bitmap Index Scan on idx_payment_status (cost=0.00..1,057.43 rows=46,533 width=0) (actual time=8.707..8.707 rows=41,929 loops=615)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
13. 0.005 0.005 ↑ 1.0 2 1

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

Planning time : 1.821 ms
Execution time : 6,501.910 ms