explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xeNj

Settings
# exclusive inclusive rows x rows loops node
1. 0.177 5,437.742 ↑ 1.0 1 1

Aggregate (cost=681,960.58..681,960.66 rows=1 width=136) (actual time=5,437.741..5,437.742 rows=1 loops=1)

2.          

CTE red_profiles

3. 0.030 5,419.317 ↑ 1.0 100 1

Limit (cost=340,236.46..656,262.10 rows=100 width=20) (actual time=3,853.332..5,419.317 rows=100 loops=1)

4. 0.253 5,419.287 ↑ 1.1 100 1

Nested Loop Left Join (cost=340,236.46..684,704.40 rows=109 width=20) (actual time=3,853.331..5,419.287 rows=100 loops=1)

5. 87.028 5,411.434 ↑ 1.1 100 1

Bitmap Heap Scan on customer_profiles cp (cost=339,772.64..634,145.05 rows=109 width=20) (actual time=3,852.949..5,411.434 rows=100 loops=1)

  • Recheck Cond: ((created_on >= '2020-07-01 00:00:00'::timestamp without time zone) AND (created_on < '2020-07-02 00:00:00'::timestamp without time zone) AND ((customer_type)::text = 'Red'::text))
  • Filter: (customer_profile_id = (SubPlan 1))
  • Rows Removed by Filter: 50,352
  • Heap Blocks: exact=9,510
6. 13.244 3,760.394 ↓ 0.0 0 1

BitmapAnd (cost=339,772.64..339,772.64 rows=21,728 width=0) (actual time=3,760.394..3,760.394 rows=0 loops=1)

7. 7.467 7.467 ↑ 1.0 81,001 1

Bitmap Index Scan on idx_created_on_customer_profiles (cost=0.00..1,208.52 rows=82,295 width=0) (actual time=7.467..7.467 rows=81,001 loops=1)

  • Index Cond: ((created_on >= '2020-07-01 00:00:00'::timestamp without time zone) AND (created_on < '2020-07-02 00:00:00'::timestamp without time zone))
8. 3,739.683 3,739.683 ↓ 1.0 31,658,931 1

Bitmap Index Scan on idx_customer_type_customer_profiles (cost=0.00..338,563.82 rows=31,556,834 width=0) (actual time=3,739.683..3,739.683 rows=31,658,931 loops=1)

  • Index Cond: ((customer_type)::text = 'Red'::text)
9.          

SubPlan (for Bitmap Heap Scan)

10. 151.356 1,564.012 ↑ 1.0 1 50,452

Aggregate (cost=12.52..12.53 rows=1 width=8) (actual time=0.031..0.031 rows=1 loops=50,452)

11. 1,412.656 1,412.656 ↓ 1.1 16 50,452

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.57..12.48 rows=15 width=8) (actual time=0.006..0.028 rows=16 loops=50,452)

  • Index Cond: (user_id = cp.user_id)
12. 0.700 7.600 ↑ 1.0 1 100

Aggregate (cost=463.82..463.83 rows=1 width=8) (actual time=0.076..0.076 rows=1 loops=100)

13. 0.664 6.900 ↑ 70.0 1 100

Nested Loop (cost=2.74..463.64 rows=70 width=8) (actual time=0.053..0.069 rows=1 loops=100)

14. 1.366 2.700 ↑ 20.1 9 100

Hash Join (cost=2.32..18.83 rows=181 width=20) (actual time=0.015..0.027 rows=9 loops=100)

  • Hash Cond: ((cpa.account_status)::text = (acs.account_status_text)::text)
15. 1.300 1.300 ↑ 20.8 9 100

Index Scan using idx_customer_profile_id_cpa on customer_profile_accounts cpa (cost=0.57..14.58 rows=187 width=31) (actual time=0.011..0.013 rows=9 loops=100)

  • Index Cond: (cp.customer_profile_id = customer_profile_id)
16. 0.012 0.034 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=520) (actual time=0.034..0.034 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.022 0.022 ↑ 1.0 33 1

Seq Scan on account_status acs (cost=0.00..1.33 rows=33 width=520) (actual time=0.017..0.022 rows=33 loops=1)

18. 3.536 3.536 ↓ 0.0 0 884

Index Scan using idx_saleable_lsa on lender_saleable_accounts lsa (cost=0.42..2.45 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=884)

  • Index Cond: ((lender_id = cpa.lender_id) AND (product_family_id = cpa.product_family_id) AND (account_status_id = acs.id))
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
19. 0.194 5,437.565 ↑ 1.0 100 1

Nested Loop Left Join (cost=256.88..25,693.23 rows=100 width=104) (actual time=3,853.593..5,437.565 rows=100 loops=1)

20. 5,419.471 5,419.471 ↑ 1.0 100 1

CTE Scan on red_profiles rl (cost=0.00..2.00 rows=100 width=20) (actual time=3,853.335..5,419.471 rows=100 loops=1)

21. 3.900 17.900 ↑ 1.0 1 100

Aggregate (cost=256.88..256.89 rows=1 width=160) (actual time=0.179..0.179 rows=1 loops=100)

22. 1.034 14.000 ↑ 1.8 12 100

Nested Loop Left Join (cost=1.83..256.54 rows=22 width=25) (actual time=0.033..0.140 rows=12 loops=100)

23. 0.767 10.500 ↑ 1.8 12 100

Nested Loop (cost=1.55..250.00 rows=22 width=21) (actual time=0.028..0.105 rows=12 loops=100)

24. 0.534 8.500 ↑ 1.8 12 100

Nested Loop Left Join (cost=1.42..246.61 rows=22 width=25) (actual time=0.025..0.085 rows=12 loops=100)

25. 0.452 5.500 ↑ 1.8 12 100

Nested Loop (cost=1.14..240.12 rows=22 width=20) (actual time=0.019..0.055 rows=12 loops=100)

26. 1.200 1.200 ↑ 2.0 3 100

Index Scan using idx_user_id_leads on leads l (cost=0.56..6.84 rows=6 width=8) (actual time=0.008..0.012 rows=3 loops=100)

  • Index Cond: (user_id = rl.user_id)
27. 3.848 3.848 ↑ 3.2 4 296

Index Scan using idx_lead_id_lead_logs on lead_logs ll (cost=0.57..38.75 rows=13 width=36) (actual time=0.007..0.013 rows=4 loops=296)

  • Index Cond: (lead_id = l.id)
  • Filter: (updated_at >= rl.profile_date)
  • Rows Removed by Filter: 1
28. 2.466 2.466 ↑ 1.0 1 1,233

Index Scan using product_status_pkey on product_status ps (cost=0.28..0.30 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1,233)

  • Index Cond: (ll.product_status_id = id)
29. 1.233 1.233 ↑ 1.0 1 1,233

Index Only Scan using lead_log_types_pkey on lead_log_types llt (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,233)

  • Index Cond: (id = ll.lead_log_type_id)
  • Heap Fetches: 0
30. 2.466 2.466 ↑ 1.0 1 1,233

Index Scan using internal_users_pkey on internal_users iu (cost=0.28..0.30 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1,233)

  • Index Cond: (ll.updated_by = id)
Planning time : 8.038 ms
Execution time : 5,441.552 ms