explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dV9s

Settings
# exclusive inclusive rows x rows loops node
1. 0.073 84.724 ↑ 1.0 1 1

Aggregate (cost=6,699.88..6,699.89 rows=1 width=32) (actual time=84.724..84.724 rows=1 loops=1)

2.          

CTE objdb

3. 0.011 84.629 ↑ 1.0 10 1

Limit (cost=6,699.50..6,699.63 rows=10 width=135) (actual time=84.617..84.629 rows=10 loops=1)

4. 3.449 84.618 ↑ 23.8 10 1

Sort (cost=6,699.50..6,700.10 rows=238 width=135) (actual time=84.616..84.618 rows=10 loops=1)

  • Sort Key: bp.create_time DESC
  • Sort Method: top-N heapsort Memory: 29kB
5. 5.629 81.169 ↓ 33.1 7,877 1

Nested Loop (cost=155.27..6,690.11 rows=238 width=135) (actual time=0.909..81.169 rows=7,877 loops=1)

6. 8.215 51.909 ↓ 33.1 7,877 1

Nested Loop (cost=154.84..6,313.90 rows=238 width=75) (actual time=0.903..51.909 rows=7,877 loops=1)

7. 9.641 27.940 ↓ 33.1 7,877 1

Nested Loop (cost=154.42..5,886.55 rows=238 width=75) (actual time=0.895..27.940 rows=7,877 loops=1)

  • Join Filter: (bp.billing_payment_state_id = bps.id)
  • Rows Removed by Join Filter: 23631
8. 5.403 10.422 ↓ 33.1 7,877 1

Hash Join (cost=154.42..5,866.63 rows=238 width=49) (actual time=0.887..10.422 rows=7,877 loops=1)

  • Hash Cond: (bp.init_obj_id = p.obj_id)
9. 4.381 4.961 ↓ 1.0 7,877 1

Bitmap Heap Scan on billing_payment bp (cost=149.07..5,840.69 rows=7,826 width=29) (actual time=0.822..4.961 rows=7,877 loops=1)

  • Recheck Cond: (billing_payment_type_id = 6)
  • Heap Blocks: exact=2043
10. 0.580 0.580 ↓ 1.0 7,878 1

Bitmap Index Scan on billing_payment_billing_payment_type_id_idx (cost=0.00..147.12 rows=7,826 width=0) (actual time=0.579..0.580 rows=7,878 loops=1)

  • Index Cond: (billing_payment_type_id = 6)
11. 0.023 0.058 ↑ 1.0 60 1

Hash (cost=4.60..4.60 rows=60 width=28) (actual time=0.058..0.058 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.035 0.035 ↑ 1.0 60 1

Seq Scan on person p (cost=0.00..4.60 rows=60 width=28) (actual time=0.006..0.035 rows=60 loops=1)

13. 7.874 7.877 ↑ 1.5 4 7,877

Materialize (cost=0.00..1.09 rows=6 width=34) (actual time=0.000..0.001 rows=4 loops=7,877)

14. 0.003 0.003 ↑ 1.5 4 1

Seq Scan on billing_payment_state bps (cost=0.00..1.06 rows=6 width=34) (actual time=0.002..0.003 rows=4 loops=1)

15. 15.754 15.754 ↑ 1.0 1 7,877

Index Scan using billing_loan_pkey on billing_loan bl (cost=0.42..1.80 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=7,877)

  • Index Cond: (obj_id = bp.billing_loan_id)
16. 23.631 23.631 ↑ 1.0 1 7,877

Index Scan using application_pkey on application app (cost=0.42..1.58 rows=1 width=66) (actual time=0.003..0.003 rows=1 loops=7,877)

  • Index Cond: (obj_id = bl.application_id)
17. 84.651 84.651 ↑ 1.0 10 1

CTE Scan on objdb (cost=0.00..0.20 rows=10 width=24) (actual time=84.628..84.651 rows=10 loops=1)