explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ixHb

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 3,856.791 ↑ 1.0 25 1

Limit (cost=3,657.52..3,657.59 rows=25 width=242) (actual time=3,856.781..3,856.791 rows=25 loops=1)

2. 26.218 3,856.781 ↑ 7.8 25 1

Sort (cost=3,657.52..3,658.01 rows=194 width=242) (actual time=3,856.780..3,856.781 rows=25 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: top-N heapsort Memory: 38kB
3. 31.727 3,830.563 ↓ 349.1 67,723 1

Group (cost=3,648.17..3,652.05 rows=194 width=242) (actual time=3,793.359..3,830.563 rows=67,723 loops=1)

  • Group Key: gh.id, gh.cid, (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name, (CASE WHEN (fa.gl_header_id IS NOT NULL) THEN 1 ELSE 0 END)
4. 53.581 3,798.836 ↓ 349.1 67,723 1

Sort (cost=3,648.17..3,648.66 rows=194 width=238) (actual time=3,793.356..3,798.836 rows=67,723 loops=1)

  • Sort Key: gh.id, (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name, (CASE WHEN (fa.gl_header_id IS NOT NULL) THEN 1 ELSE 0 END)
  • Sort Method: quicksort Memory: 20408kB
5. 0.000 3,745.255 ↓ 349.1 67,723 1

Nested Loop Left Join (cost=15.45..3,640.80 rows=194 width=238) (actual time=7.785..3,745.255 rows=67,723 loops=1)

6. 50.569 3,547.736 ↓ 349.1 67,723 1

Nested Loop (cost=13.12..3,175.68 rows=194 width=234) (actual time=7.763..3,547.736 rows=67,723 loops=1)

7. 21.802 185.305 ↓ 371.1 71,997 1

Hash Join (cost=1.83..966.38 rows=194 width=142) (actual time=0.062..185.305 rows=71,997 loops=1)

  • Hash Cond: (gh.gl_book_id = gb.id)
8. 163.489 163.489 ↓ 186.0 71,997 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.032..163.489 rows=71,997 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
9. 0.007 0.014 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 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.006..0.007 rows=4 loops=1)

  • Filter: (cid = 2603)
  • Rows Removed by Filter: 4
11. 575.976 3,311.862 ↑ 1.0 1 71,997

Aggregate (cost=11.30..11.33 rows=1 width=97) (actual time=0.046..0.046 rows=1 loops=71,997)

  • Filter: (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END) IS NOT NULL)
  • Rows Removed by Filter: 0
12. 541.247 2,735.886 ↓ 13.0 13 71,997

Nested Loop (cost=2.62..11.27 rows=1 width=38) (actual time=0.008..0.038 rows=13 loops=71,997)

13. 208.378 1,223.949 ↓ 13.0 13 71,997

Hash Semi Join (cost=2.35..9.20 rows=1 width=25) (actual time=0.007..0.017 rows=13 loops=71,997)

  • Hash Cond: (gd.property_id = lp.property_id)
14. 1,007.958 1,007.958 ↓ 3.8 15 71,997

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..7.28 rows=4 width=21) (actual time=0.006..0.014 rows=15 loops=71,997)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((cid = 2603) AND (gl_transaction_type_id = gh.gl_transaction_type_id))
15. 0.059 7.613 ↓ 464.0 464 1

Hash (cost=1.88..1.88 rows=1 width=8) (actual time=7.612..7.613 rows=464 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
16. 7.554 7.554 ↓ 464.0 464 1

Function Scan on load_properties lp (cost=0.25..1.88 rows=1 width=8) (actual time=7.499..7.554 rows=464 loops=1)

  • Filter: (cid = 2603)
17. 970.690 970.690 ↑ 1.0 1 970,690

Index Scan using idx_properties_id on properties p (cost=0.28..2.07 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=970,690)

  • Index Cond: (id = gd.property_id)
  • Filter: ((cid = 2603) AND (is_disabled = 0))
18. 67.723 203.169 ↓ 0.0 0 67,723

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

19. 67.723 135.446 ↓ 0.0 0 67,723

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

  • Sort Key: fa.id DESC
  • Sort Method: quicksort Memory: 25kB
20. 67.723 67.723 ↓ 0.0 0 67,723

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.001..0.001 rows=0 loops=67,723)

  • Index Cond: ((gh.cid = cid) AND (gh.id = gl_header_id))
  • Filter: (deleted_by IS NULL)
Planning time : 5.380 ms
Execution time : 3,858.586 ms