explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DdBG

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 38,485.893 ↑ 1.0 25 1

Limit (cost=2,591.16..2,591.22 rows=25 width=206) (actual time=38,485.884..38,485.893 rows=25 loops=1)

2. 27.041 38,485.887 ↑ 7.8 25 1

Sort (cost=2,591.16..2,591.64 rows=194 width=206) (actual time=38,485.883..38,485.887 rows=25 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: top-N heapsort Memory: 34kB
3. 17.033 38,458.846 ↓ 349.1 67,728 1

Nested Loop Left Join (cost=5.24..2,585.68 rows=194 width=206) (actual time=8.146..38,458.846 rows=67,728 loops=1)

4. 24.035 38,238.629 ↓ 349.1 67,728 1

Nested Loop (cost=2.91..2,120.57 rows=194 width=202) (actual time=8.116..38,238.629 rows=67,728 loops=1)

5. 24.812 197.538 ↓ 371.1 72,002 1

Hash Join (cost=1.83..966.38 rows=194 width=142) (actual time=0.054..197.538 rows=72,002 loops=1)

  • Hash Cond: (gh.gl_book_id = gb.id)
6. 172.715 172.715 ↓ 186.1 72,002 1

Index Scan using idx_gl_headers_cid_gl_transaction_type_id_post_month_reference_ on gl_headers gh (cost=0.43..962.48 rows=387 width=134) (actual time=0.030..172.715 rows=72,002 loops=1)

  • Index Cond: ((cid = 2603) AND (gl_transaction_type_id = 1) AND (reference_id IS NULL))
  • Filter: ((is_template IS FALSE) AND (gl_header_status_type_id = ANY ('{5,2,1,3}'::integer[])))
  • Rows Removed by Filter: 2811
7. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.26..1.26 rows=4 width=16) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on gl_books gb (cost=0.00..1.26 rows=4 width=16) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: (cid = 2603)
  • Rows Removed by Filter: 4
9. 504.014 38,017.056 ↑ 1.0 1 72,002

GroupAggregate (cost=1.09..5.89 rows=1 width=105) (actual time=0.528..0.528 rows=1 loops=72,002)

  • Group Key: gh.cid, gh.id
  • Filter: (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END) IS NOT NULL)
10. 15,285.404 37,513.042 ↓ 13.0 13 72,002

Nested Loop Semi Join (cost=1.09..5.84 rows=1 width=21) (actual time=0.041..0.521 rows=13 loops=72,002)

  • Join Filter: (gd.property_id = lp.property_id)
  • Rows Removed by Join Filter: 3212
11. 671.708 2,808.078 ↓ 13.0 13 72,002

Nested Loop (cost=0.84..3.93 rows=1 width=25) (actual time=0.010..0.039 rows=13 loops=72,002)

12. 1,080.030 1,080.030 ↓ 15.0 15 72,002

Index Scan using idx_gl_details_cid_gl_transaction_type_id_post_month_gl_header_ on gl_details gd (cost=0.56..2.61 rows=1 width=21) (actual time=0.006..0.015 rows=15 loops=72,002)

  • Index Cond: ((cid = 2603) AND (gl_transaction_type_id = gh.gl_transaction_type_id) AND (post_month = gh.post_month) AND (gh.id = gl_header_id))
13. 1,056.340 1,056.340 ↑ 1.0 1 1,056,340

Index Only Scan using idx_properties_cid_id_is_disabled_remote_primary_key on properties p (cost=0.28..1.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,056,340)

  • Index Cond: ((cid = 2603) AND (id = gd.property_id) AND (is_disabled = 0))
  • Heap Fetches: 183802
14. 19,419.560 19,419.560 ↓ 239.0 239 970,978

Function Scan on load_properties lp (cost=0.25..1.88 rows=1 width=8) (actual time=0.000..0.020 rows=239 loops=970,978)

  • Filter: (cid = 2603)
15. 0.000 203.184 ↓ 0.0 0 67,728

Limit (cost=2.33..2.34 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=67,728)

16. 67.728 203.184 ↓ 0.0 0 67,728

Sort (cost=2.33..2.34 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=67,728)

  • Sort Key: fa.id DESC
  • Sort Method: quicksort Memory: 25kB
17. 135.456 135.456 ↓ 0.0 0 67,728

Index Scan using idx_file_associations_gl_header_id_partial on file_associations fa (cost=0.28..2.32 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=67,728)

  • Index Cond: ((gh.cid = cid) AND (gh.id = gl_header_id))
  • Filter: (deleted_by IS NULL)
Planning time : 5.571 ms
Execution time : 38,486.643 ms