explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XhU

Settings
# exclusive inclusive rows x rows loops node
1. 0.534 1,853.761 ↑ 41.4 9 1

Subquery Scan on f (cost=5,820.85..2,517,639.86 rows=373 width=50) (actual time=85.278..1,853.761 rows=9 loops=1)

  • Filter: (f.summ <= '200000'::numeric)
  • Rows Removed by Filter: 1,124
2. 4.454 1,853.227 ↓ 1.0 1,133 1

WindowAgg (cost=5,820.85..2,517,625.89 rows=1,118 width=50) (actual time=85.277..1,853.227 rows=1,133 loops=1)

3. 28.797 1,848.773 ↓ 1.0 1,133 1

Subquery Scan on d (cost=5,820.85..2,517,603.53 rows=1,118 width=18) (actual time=84.526..1,848.773 rows=1,133 loops=1)

  • Filter: (((SubPlan 4) < '2019-10-1'::text) AND ((SubPlan 3) < '2019-10-01 00:00:00+00'::timestamp with time zone) AND (((SubPlan 1) > '500'::numeric) OR ((SubPlan 2) < (abs(d.body) * 0.2))))
  • Rows Removed by Filter: 13,939
4. 20.719 67.041 ↑ 1.2 15,072 1

Sort (cost=5,820.85..5,866.14 rows=18,117 width=18) (actual time=62.547..67.041 rows=15,072 loops=1)

  • Sort Key: bl.balance, bl.obj_id
  • Sort Method: quicksort Memory: 1,562kB
5. 15.124 46.322 ↑ 1.2 15,072 1

HashAggregate (cost=4,358.35..4,539.52 rows=18,117 width=18) (actual time=40.868..46.322 rows=15,072 loops=1)

  • Group Key: bl.obj_id
6. 29.404 31.198 ↑ 1.2 15,072 1

Bitmap Heap Scan on billing_loan bl (cost=371.83..4,313.05 rows=18,117 width=18) (actual time=2.984..31.198 rows=15,072 loops=1)

  • Recheck Cond: (billing_loan_state_id = ANY ('{1,26}'::integer[]))
  • Filter: (term_end_date < (now())::date)
  • Rows Removed by Filter: 5,377
  • Heap Blocks: exact=1,612
7. 1.794 1.794 ↓ 1.0 20,449 1

Bitmap Index Scan on billing_loan_state_id (cost=0.00..367.31 rows=20,061 width=0) (actual time=1.794..1.794 rows=20,449 loops=1)

  • Index Cond: (billing_loan_state_id = ANY ('{1,26}'::integer[]))
8.          

SubPlan (for Subquery Scan)

9. 15.072 195.936 ↑ 1.0 1 15,072

Aggregate (cost=17.37..17.38 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=15,072)

10. 13.096 180.864 ↓ 0.0 0 15,072

Nested Loop (cost=0.58..17.37 rows=1 width=10) (actual time=0.011..0.012 rows=0 loops=15,072)

11. 60.288 60.288 ↑ 1.0 1 15,072

Index Scan using collection_billing_loan_id on collection c (cost=0.29..8.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=15,072)

  • Index Cond: (billing_loan_id = d.obj_id)
12. 107.480 107.480 ↓ 0.0 0 10,748

Index Scan using collection_report_collection_id_idx on collection_report cr (cost=0.29..9.05 rows=1 width=14) (actual time=0.009..0.010 rows=0 loops=10,748)

  • Index Cond: (collection_id = c.id)
  • Filter: (collection_report_result_id = ANY ('{2,10}'::integer[]))
  • Rows Removed by Filter: 1
13. 4.818 1,139.457 ↑ 1.0 1 2,409

Aggregate (cost=40.00..40.01 rows=1 width=8) (actual time=0.473..0.473 rows=1 loops=2,409)

14. 1,134.639 1,134.639 ↑ 1.2 4 2,409

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo_2 (cost=0.43..39.99 rows=5 width=8) (actual time=0.173..0.471 rows=4 loops=2,409)

  • Index Cond: (billing_loan_id = d.obj_id)
  • Filter: (billing_loan_operation_type_id = ANY ('{6,8,9}'::integer[]))
  • Rows Removed by Filter: 141
15. 10.020 303.940 ↑ 1.0 1 1,670

Aggregate (cost=40.60..40.61 rows=1 width=32) (actual time=0.182..0.182 rows=1 loops=1,670)

16. 293.920 293.920 ↓ 4.5 9 1,670

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo (cost=0.43..40.59 rows=2 width=5) (actual time=0.169..0.176 rows=9 loops=1,670)

  • Index Cond: (billing_loan_id = d.obj_id)
  • Filter: (create_time > ((now())::date - 25))
  • Rows Removed by Filter: 157
17. 2.876 113.602 ↑ 1.0 1 719

Aggregate (cost=40.60..40.61 rows=1 width=32) (actual time=0.158..0.158 rows=1 loops=719)

18. 110.726 110.726 ↓ 2.0 4 719

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo_1 (cost=0.43..40.59 rows=2 width=5) (actual time=0.152..0.154 rows=4 loops=719)

  • Index Cond: (billing_loan_id = d.obj_id)
  • Filter: (create_time > ((now())::date - 25))
  • Rows Removed by Filter: 179
Planning time : 2.074 ms
Execution time : 1,854.236 ms