explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F1yN

Settings
# exclusive inclusive rows x rows loops node
1. 3,277.710 18,239.292 ↑ 1.0 100,000 1

Nested Loop Left Join (cost=20,974.22..11,963,402.94 rows=100,000 width=2,431) (actual time=484.062..18,239.292 rows=100,000 loops=1)

2.          

CTE base

3. 16.876 81.689 ↑ 1.0 100,000 1

Limit (cost=0.42..20,879.73 rows=100,000 width=428) (actual time=0.033..81.689 rows=100,000 loops=1)

4. 64.813 64.813 ↑ 3.5 100,000 1

Index Scan using is_lender_base_collections_leads on collections_leads (cost=0.42..73,929.03 rows=354,076 width=428) (actual time=0.032..64.813 rows=100,000 loops=1)

  • Index Cond: (is_lender_base = 0)
5. 184.640 184.640 ↑ 1.0 100,000 1

CTE Scan on base col_l (cost=0.00..2,000.00 rows=100,000 width=1,674) (actual time=0.034..184.640 rows=100,000 loops=1)

6. 3,300.000 9,700.000 ↑ 1.0 1 100,000

Aggregate (cost=94.49..94.50 rows=1 width=17) (actual time=0.097..0.097 rows=1 loops=100,000)

7. 299.981 6,400.000 ↑ 4.8 8 100,000

Hash Join (cost=2.96..93.83 rows=38 width=17) (actual time=0.024..0.064 rows=8 loops=100,000)

  • Hash Cond: (ll.lead_log_type_id = llt.id)
8. 658.538 6,100.000 ↑ 4.8 8 100,000

Nested Loop Left Join (cost=1.42..91.83 rows=38 width=21) (actual time=0.023..0.061 rows=8 loops=100,000)

9. 300.168 3,900.000 ↑ 4.8 8 100,000

Nested Loop (cost=1.14..41.62 rows=38 width=16) (actual time=0.020..0.039 rows=8 loops=100,000)

10. 800.000 800.000 ↑ 1.0 1 100,000

Index Only Scan using leads_pkey on leads l (cost=0.56..2.58 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=100,000)

  • Index Cond: (id = col_l.lead_id)
  • Heap Fetches: 67,956
11. 2,799.832 2,799.832 ↑ 4.8 8 99,994

Index Scan using idx_lead_id_lead_logs on lead_logs ll (cost=0.57..38.65 rows=38 width=36) (actual time=0.011..0.028 rows=8 loops=99,994)

  • Index Cond: (lead_id = col_l.lead_id)
12. 1,541.462 1,541.462 ↑ 1.0 1 770,731

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

  • Index Cond: (ll.product_status_id = id)
13. 0.008 0.019 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=4) (actual time=0.019..0.019 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.011 0.011 ↑ 1.0 24 1

Seq Scan on lead_log_types llt (cost=0.00..1.24 rows=24 width=4) (actual time=0.005..0.011 rows=24 loops=1)

15.          

SubPlan (for Nested Loop Left Join)

16. 0.000 800.000 ↑ 1.0 1 100,000

Limit (cost=0.43..2.45 rows=1 width=7) (actual time=0.008..0.008 rows=1 loops=100,000)

17. 800.000 800.000 ↑ 1.0 1 100,000

Index Scan using user_inputs__index_on_user_id on user_inputs (cost=0.43..2.45 rows=1 width=7) (actual time=0.008..0.008 rows=1 loops=100,000)

  • Index Cond: (user_id = col_l.user_id)
18. 41.508 373.572 ↑ 1.0 1 41,508

Limit (cost=0.43..2.45 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=41,508)

19. 332.064 332.064 ↑ 1.0 1 41,508

Index Scan using user_income_user_id on user_income (cost=0.43..2.45 rows=1 width=7) (actual time=0.008..0.008 rows=1 loops=41,508)

  • Index Cond: (user_id = col_l.user_id)
20. 100.000 3,700.000 ↑ 1.0 1 100,000

Limit (cost=17.08..17.08 rows=1 width=48) (actual time=0.037..0.037 rows=1 loops=100,000)

21. 300.000 3,600.000 ↑ 5.0 1 100,000

Sort (cost=17.08..17.09 rows=5 width=48) (actual time=0.036..0.036 rows=1 loops=100,000)

  • Sort Key: user_tokens.modified_ts DESC
  • Sort Method: quicksort Memory: 25kB
22. 3,300.000 3,300.000 ↑ 1.2 4 100,000

Index Scan using idx_user_id_user_tokens on user_tokens (cost=0.57..17.06 rows=5 width=48) (actual time=0.011..0.033 rows=4 loops=100,000)

  • Index Cond: (user_id = col_l.user_id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 17
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using usl_currentmonth_composite_user_id_created_at_idx on usl_currentmonth (cost=0.44..2.46 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = col_l.user_id) AND (created_at >= (CURRENT_DATE - '7 days'::interval)))
  • Heap Fetches: 0
24. 203.370 203.370 ↓ 1.2 831,297 1

Index Scan using usl_currentmonth_created_at_idx on usl_currentmonth usl_currentmonth_1 (cost=0.44..23,169.48 rows=722,174 width=8) (actual time=0.035..203.370 rows=831,297 loops=1)

  • Index Cond: (created_at >= (CURRENT_DATE - '7 days'::interval))
Planning time : 3.860 ms
Execution time : 18,269.789 ms