explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dstK

Settings
# exclusive inclusive rows x rows loops node
1. 0.660 4,193.524 ↑ 13.8 9 1

Subquery Scan on totalbalancelimitcondition (cost=5,707.31..3,501,161.38 rows=124 width=50) (actual time=79.461..4,193.524 rows=9 loops=1)

  • Filter: (totalbalancelimitcondition.total_balance <= 200000.000000)
  • Rows Removed by Filter: 1378
2. 4.553 4,192.864 ↓ 3.7 1,387 1

WindowAgg (cost=5,707.31..3,501,156.72 rows=373 width=50) (actual time=79.460..4,192.864 rows=1,387 loops=1)

3. 63.107 4,188.311 ↓ 3.7 1,387 1

Subquery Scan on firstconditionset (cost=5,707.31..3,501,149.26 rows=373 width=18) (actual time=45.212..4,188.311 rows=1,387 loops=1)

  • Filter: ((((SubPlan 4) <= '2019-09-08'::text) OR ((SubPlan 5) IS NULL)) AND ((SubPlan 6) < (abs(firstconditionset.body) * 0.200000)) AND ((SubPlan 3) < '2019-09-08 00:00:00+00'::timestamp with time zone) AND (((SubPlan 1) < (abs(firstconditionset.body) * 0.200000)) OR ((SubPlan 2) < 500.000000)))
  • Rows Removed by Filter: 12605
4. 21.530 45.667 ↑ 1.3 13,992 1

Sort (cost=5,707.31..5,752.23 rows=17,971 width=18) (actual time=40.326..45.667 rows=13,992 loops=1)

  • Sort Key: bl.balance, bl.obj_id
  • Sort Method: quicksort Memory: 1478kB
5. 14.320 24.137 ↑ 1.3 13,992 1

HashAggregate (cost=4,257.64..4,437.35 rows=17,971 width=18) (actual time=17.161..24.137 rows=13,992 loops=1)

  • Group Key: bl.obj_id
6. 8.809 9.817 ↑ 1.3 13,992 1

Bitmap Heap Scan on billing_loan bl (cost=371.80..4,212.71 rows=17,971 width=18) (actual time=1.206..9.817 rows=13,992 loops=1)

  • Recheck Cond: (billing_loan_state_id = ANY ('{1,26}'::integer[]))
  • Filter: (term_end_date < '2019-09-28'::date)
  • Rows Removed by Filter: 6457
  • Heap Blocks: exact=1612
7. 1.008 1.008 ↓ 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.008..1.008 rows=20,449 loops=1)

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

SubPlan (forSubquery Scan)

9. 13.992 209.880 ↑ 1.0 1 13,992

Aggregate (cost=17.37..17.38 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=13,992)

10. 8.767 195.888 ↓ 0.0 0 13,992

Nested Loop (cost=0.58..17.37 rows=1 width=10) (actual time=0.013..0.014 rows=0 loops=13,992)

11. 69.960 69.960 ↑ 1.0 1 13,992

Index Scan using collection_billing_loan_id on collection c (cost=0.29..8.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=13,992)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
12. 117.161 117.161 ↓ 0.0 0 10,651

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.011 rows=0 loops=10,651)

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

Aggregate (cost=17.37..17.38 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=11,886)

14. 6.682 47.544 ↓ 0.0 0 11,886

Nested Loop (cost=0.58..17.37 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=11,886)

15. 23.772 23.772 ↑ 1.0 1 11,886

Index Scan using collection_billing_loan_id on collection c_1 (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=11,886)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
16. 17.090 17.090 ↓ 0.0 0 8,545

Index Scan using collection_report_collection_id_idx on collection_report cr_1 (cost=0.29..9.05 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=8,545)

  • Index Cond: (collection_id = c_1.id)
  • Filter: (collection_report_result_id = ANY ('{2,10}'::integer[]))
  • Rows Removed by Filter: 1
17. 55.016 3,562.286 ↑ 1.0 1 13,754

Aggregate (cost=39.89..39.90 rows=1 width=32) (actual time=0.259..0.259 rows=1 loops=13,754)

18. 3,507.270 3,507.270 ↓ 4.0 8 13,754

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo_3 (cost=0.43..39.87 rows=2 width=5) (actual time=0.243..0.255 rows=8 loops=13,754)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
  • Filter: (create_time > '2019-09-08 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 96
19. 3.345 160.560 ↑ 1.0 1 3,345

Aggregate (cost=40.00..40.01 rows=1 width=8) (actual time=0.048..0.048 rows=1 loops=3,345)

20. 157.215 157.215 ↑ 2.5 2 3,345

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.022..0.047 rows=2 loops=3,345)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
  • Filter: (billing_loan_operation_type_id = ANY ('{9,6,8}'::integer[]))
  • Rows Removed by Filter: 70
21. 5.548 87.381 ↑ 1.0 1 1,387

Aggregate (cost=39.89..39.90 rows=1 width=32) (actual time=0.063..0.063 rows=1 loops=1,387)

22. 81.833 81.833 ↓ 4.5 9 1,387

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo (cost=0.43..39.87 rows=2 width=5) (actual time=0.054..0.059 rows=9 loops=1,387)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
  • Filter: (create_time >= '2019-09-08 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 113
23. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=39.89..39.90 rows=1 width=32) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using billing_loan_operation_billing_loan_id_idx on billing_loan_operation t_blo_1 (cost=0.43..39.87 rows=2 width=5) (never executed)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
  • Filter: (create_time >= '2019-09-08 00:00:00+00'::timestamp with time zone)
Planning time : 1.447 ms
Execution time : 4,193.713 ms