explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w2EV

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 69,123.955 ↑ 1.0 25 1

Limit (cost=14,497.08..14,497.15 rows=25 width=241) (actual time=69,123.927..69,123.955 rows=25 loops=1)

2.          

CTE properties_cte

3. 36.235 36.235 ↓ 9.4 468 1

Function Scan on load_properties lp (cost=0.25..0.75 rows=50 width=4) (actual time=36.038..36.235 rows=468 loops=1)

4. 53.282 69,123.934 ↑ 12.6 25 1

Sort (cost=14,496.33..14,497.12 rows=316 width=241) (actual time=69,123.924..69,123.934 rows=25 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: top-N heapsort Memory: 38kB
5. 85.137 69,070.652 ↓ 227.3 71,832 1

Group (cost=14,481.10..14,487.42 rows=316 width=241) (actual time=68,955.598..69,070.652 rows=71,832 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)
6. 193.012 68,985.515 ↓ 227.3 71,832 1

Sort (cost=14,481.10..14,481.89 rows=316 width=237) (actual time=68,955.584..68,985.515 rows=71,832 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: 21,496kB
7. 154.497 68,792.503 ↓ 227.3 71,832 1

Nested Loop Left Join (cost=35.22..14,467.98 rows=316 width=237) (actual time=42.040..68,792.503 rows=71,832 loops=1)

8. 131.783 65,405.566 ↓ 227.3 71,832 1

Nested Loop (cost=26.90..11,833.33 rows=316 width=233) (actual time=41.638..65,405.566 rows=71,832 loops=1)

9. 214.791 16,693.724 ↓ 243.6 76,989 1

Nested Loop (cost=0.56..3,498.11 rows=316 width=141) (actual time=3.242..16,693.724 rows=76,989 loops=1)

  • Join Filter: (gh.gl_book_id = gb.id)
  • Rows Removed by Join Filter: 153,971
10. 16,401.944 16,401.944 ↓ 121.8 76,989 1

Index Scan using idx_gl_headers_cid_gl_transaction_type_id_post_month_reference_ on gl_headers gh (cost=0.56..3,461.93 rows=632 width=133) (actual time=2.669..16,401.944 rows=76,989 loops=1)

  • Index Cond: ((cid = 2,603) 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: 2,991
11. 76.429 76.989 ↑ 1.3 3 76,989

Materialize (cost=0.00..1.12 rows=4 width=16) (actual time=0.001..0.001 rows=3 loops=76,989)

12. 0.560 0.560 ↑ 1.3 3 1

Seq Scan on gl_books gb (cost=0.00..1.10 rows=4 width=16) (actual time=0.558..0.560 rows=3 loops=1)

  • Filter: (cid = 2,603)
  • Rows Removed by Filter: 3
13. 1,308.813 48,580.059 ↑ 1.0 1 76,989

Aggregate (cost=26.34..26.36 rows=1 width=97) (actual time=0.630..0.631 rows=1 loops=76,989)

  • Filter: (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END) IS NOT NULL)
  • Rows Removed by Filter: 0
14. 1,196.326 47,271.246 ↓ 13.0 13 76,989

Nested Loop (cost=2.34..26.32 rows=1 width=38) (actual time=0.317..0.614 rows=13 loops=76,989)

  • Join Filter: (gd.property_id = p.id)
15. 1,194.974 44,037.708 ↓ 13.0 13 76,989

Hash Join (cost=2.06..23.21 rows=1 width=25) (actual time=0.312..0.572 rows=13 loops=76,989)

  • Hash Cond: (gd.property_id = p_temp.property_id)
16. 42,805.884 42,805.884 ↓ 3.5 14 76,989

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..21.56 rows=4 width=21) (actual time=0.300..0.556 rows=14 loops=76,989)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((cid = 2,603) AND (gl_transaction_type_id = gh.gl_transaction_type_id))
17. 0.210 36.850 ↓ 9.4 468 1

Hash (cost=1.00..1.00 rows=50 width=4) (actual time=36.850..36.850 rows=468 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
18. 36.640 36.640 ↓ 9.4 468 1

CTE Scan on properties_cte p_temp (cost=0.00..1.00 rows=50 width=4) (actual time=36.041..36.640 rows=468 loops=1)

19. 2,037.212 2,037.212 ↑ 1.0 1 1,018,606

Index Scan using idx_properties_id on properties p (cost=0.28..3.10 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=1,018,606)

  • Index Cond: (id = p_temp.property_id)
  • Filter: ((cid = 2,603) AND (is_disabled = 0))
20. 71.832 3,232.440 ↓ 0.0 0 71,832

Limit (cost=8.31..8.32 rows=1 width=8) (actual time=0.045..0.045 rows=0 loops=71,832)

21. 215.496 3,160.608 ↓ 0.0 0 71,832

Sort (cost=8.31..8.32 rows=1 width=8) (actual time=0.044..0.044 rows=0 loops=71,832)

  • Sort Key: fa.id DESC
  • Sort Method: quicksort Memory: 25kB
22. 2,945.112 2,945.112 ↓ 0.0 0 71,832

Index Scan using idx_file_associations_gl_header_id_partial on file_associations fa (cost=0.28..8.30 rows=1 width=8) (actual time=0.037..0.041 rows=0 loops=71,832)

  • Index Cond: ((gh.cid = cid) AND (gh.id = gl_header_id))
  • Filter: (deleted_by IS NULL)