explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gup6

Settings
# exclusive inclusive rows x rows loops node
1. 0.589 4,299.216 ↑ 13.8 9 1

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

  • Filter: (totalbalancelimitcondition.total_balance <= 200000.000000)
  • Rows Removed by Filter: 1375
2. 4.565 4,298.627 ↓ 3.7 1,384 1

WindowAgg (cost=5,707.31..3,501,201.64 rows=373 width=50) (actual time=100.163..4,298.627 rows=1,384 loops=1)

3. 59.227 4,294.062 ↓ 3.7 1,384 1

Subquery Scan on firstconditionset (cost=5,707.31..3,501,194.18 rows=373 width=18) (actual time=70.636..4,294.062 rows=1,384 loops=1)

  • Filter: ((((SubPlan 4) <= '2019-09-08'::text) OR ((SubPlan 5) = 0)) 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: 12608
4. 27.155 71.480 ↑ 1.3 13,992 1

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

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

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

  • Group Key: bl.obj_id
6. 19.555 21.783 ↑ 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=2.488..21.783 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. 2.228 2.228 ↓ 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=2.228..2.228 rows=20,449 loops=1)

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

SubPlan (forSubquery Scan)

9. 13.992 223.872 ↑ 1.0 1 13,992

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

10. 22.759 209.880 ↓ 0.0 0 13,992

Nested Loop (cost=0.58..17.37 rows=1 width=10) (actual time=0.014..0.015 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.010..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=8) (actual time=0.005..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=0) (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=4) (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. 54.596 3,630.634 ↑ 1.0 1 13,649

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

18. 3,576.038 3,576.038 ↓ 4.0 8 13,649

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.250..0.262 rows=8 loops=13,649)

  • 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.339 163.611 ↑ 1.0 1 3,339

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

20. 160.272 160.272 ↑ 2.5 2 3,339

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.023..0.048 rows=2 loops=3,339)

  • 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.536 85.808 ↑ 1.0 1 1,384

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

22. 80.272 80.272 ↓ 4.5 9 1,384

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.053..0.058 rows=9 loops=1,384)

  • 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: 112
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 : 3.005 ms
Execution time : 4,299.876 ms