explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Oiu

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 72.683 ↓ 0.0 0 1

Subquery Scan on totalbalancelimitcondition (cost=5,707.31..1,591,456.09 rows=124 width=50) (actual time=72.683..72.683 rows=0 loops=1)

  • Filter: (totalbalancelimitcondition.total_balance <= 200000.000000)
2. 0.001 72.682 ↓ 0.0 0 1

WindowAgg (cost=5,707.31..1,591,451.43 rows=373 width=50) (actual time=72.682..72.682 rows=0 loops=1)

3. 10.597 72.681 ↓ 0.0 0 1

Subquery Scan on firstconditionset (cost=5,707.31..1,591,443.97 rows=373 width=18) (actual time=72.681..72.681 rows=0 loops=1)

  • Filter: (((SubPlan 6) < (abs(firstconditionset.body) * 0.200000)) AND (((SubPlan 1) < (abs(firstconditionset.body) * 1.200000)) OR ((SubPlan 2) < 555500.000000)) AND (((SubPlan 4) <= '2019-09-28'::text) OR ((SubPlan 5) IS NULL)) AND ((SubPlan 3) < '2019-09-28 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 13992
4. 15.600 34.100 ↑ 1.3 13,992 1

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

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

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

  • Group Key: bl.obj_id
6. 7.105 8.078 ↑ 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.219..8.078 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. 0.973 0.973 ↓ 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=0.973..0.973 rows=20,449 loops=1)

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

SubPlan (forSubquery Scan)

9. 13.992 27.984 ↑ 1.0 1 13,992

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=13,992)

10. 13.992 13.992 ↓ 0.0 0 13,992

Index Scan using billing_loan_operation_create_time_index on billing_loan_operation t_blo_3 (cost=0.43..4.46 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=13,992)

  • Index Cond: (create_time > '2019-09-28 00:00:00+00'::timestamp with time zone)
  • Filter: (billing_loan_id = firstconditionset.obj_id)
11. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using billing_loan_operation_create_time_index on billing_loan_operation t_blo (cost=0.43..4.46 rows=1 width=5) (never executed)

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

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using billing_loan_operation_create_time_index on billing_loan_operation t_blo_1 (cost=0.43..4.46 rows=1 width=5) (never executed)

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

Aggregate (cost=17.37..17.38 rows=1 width=32) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..17.37 rows=1 width=10) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using collection_billing_loan_id on collection c (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using collection_report_collection_id_idx on collection_report cr (cost=0.29..9.05 rows=1 width=14) (never executed)

  • Index Cond: (collection_id = c.id)
  • Filter: (collection_report_result_id = ANY ('{2,10}'::integer[]))
19. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=17.37..17.38 rows=1 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..17.37 rows=1 width=10) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using collection_billing_loan_id on collection c_1 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using collection_report_collection_id_idx on collection_report cr_1 (cost=0.29..9.05 rows=1 width=14) (never executed)

  • Index Cond: (collection_id = c_1.id)
  • Filter: (collection_report_result_id = ANY ('{2,10}'::integer[]))
23. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=40.00..40.01 rows=1 width=8) (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_2 (cost=0.43..39.99 rows=5 width=8) (never executed)

  • Index Cond: (billing_loan_id = firstconditionset.obj_id)
  • Filter: (billing_loan_operation_type_id = ANY ('{9,6,8}'::integer[]))
Planning time : 1.317 ms
Execution time : 72.872 ms