explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Etlz

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 8,637.516 ↓ 2.0 2 1

Limit (cost=1,296,343.31..1,296,343.32 rows=1 width=714) (actual time=8,637.507..8,637.516 rows=2 loops=1)

2. 0.014 8,637.513 ↓ 2.0 2 1

Sort (cost=1,296,343.31..1,296,343.32 rows=1 width=714) (actual time=8,637.505..8,637.513 rows=2 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.004 8,637.499 ↓ 2.0 2 1

Group (cost=1,296,343.28..1,296,343.30 rows=1 width=714) (actual time=8,637.488..8,637.499 rows=2 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
4. 0.019 8,637.495 ↓ 2.0 2 1

Sort (cost=1,296,343.28..1,296,343.29 rows=1 width=710) (actual time=8,637.486..8,637.495 rows=2 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
  • Sort Method: quicksort Memory: 25kB
5. 0.008 8,637.476 ↓ 2.0 2 1

Nested Loop (cost=1,296,341.88..1,296,343.27 rows=1 width=710) (actual time=8,637.419..8,637.476 rows=2 loops=1)

6. 0.012 8,627.050 ↓ 2.0 2 1

Hash Join (cost=1,296,330.98..1,296,332.28 rows=1 width=618) (actual time=8,627.039..8,627.050 rows=2 loops=1)

  • Hash Cond: (gb.id = gh.gl_book_id)
7. 0.012 0.012 ↑ 1.0 4 1

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

  • Filter: (cid = 2,603)
  • Rows Removed by Filter: 4
8. 0.009 8,627.026 ↓ 2.0 2 1

Hash (cost=1,296,330.94..1,296,330.94 rows=1 width=610) (actual time=8,627.020..8,627.026 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 738.883 8,627.017 ↓ 2.0 2 1

Index Scan using idx_gl_headers_gl_header_status_type_id on gl_headers gh (cost=1,296,327.46..1,296,330.94 rows=1 width=610) (actual time=8,626.996..8,627.017 rows=2 loops=1)

  • Index Cond: (gl_header_status_type_id = ANY ('{2,5}'::integer[]))
  • Filter: ((is_template IS FALSE) AND (reference_id IS NULL) AND (NOT (hashed SubPlan 1)) AND (cid = 2,603) AND (gl_transaction_type_id = 1))
10.          

SubPlan (for Index Scan)

11. 3,536.584 7,888.134 ↓ 2,657,122.0 2,657,122 1

Hash Anti Join (cost=215.46..1,296,327.02 rows=1 width=4) (actual time=64.255..7,888.134 rows=2,657,122 loops=1)

  • Hash Cond: ((gd_1.cid = p_1.cid) AND (gd_1.property_id = p_1.id))
12. 4,350.750 4,350.750 ↓ 1.0 25,157,617 1

Index Only Scan using idx_gl_details_cid_gl_transaction_type_id_post_month_property_i on gl_details gd_1 (cost=0.56..1,107,640.36 rows=25,129,568 width=12) (actual time=0.030..4,350.750 rows=25,157,617 loops=1)

  • Index Cond: (cid = 2,603)
  • Heap Fetches: 59,425
13. 0.064 0.800 ↓ 1.0 474 1

Hash (cost=198.34..198.34 rows=473 width=8) (actual time=0.796..0.800 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
14. 0.049 0.736 ↓ 1.0 474 1

Nested Loop (cost=154.33..198.34 rows=473 width=8) (actual time=0.538..0.736 rows=474 loops=1)

15. 0.012 0.012 ↑ 1.0 1 1

Index Scan using pk_company_users on company_users cu (cost=0.29..2.33 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((cid = 2,603) AND (id = 6,948))
  • Filter: (company_user_type_id = 2)
16. 0.095 0.675 ↓ 1.0 474 1

Hash Join (cost=154.04..181.82 rows=473 width=8) (actual time=0.526..0.675 rows=474 loops=1)

  • Hash Cond: (pgs.property_id = p_1.id)
17. 0.082 0.082 ↑ 1.0 542 1

Index Only Scan using uk_property_gl_settings_cid_property_id on property_gl_settings pgs (cost=0.28..26.60 rows=542 width=8) (actual time=0.021..0.082 rows=542 loops=1)

  • Index Cond: (cid = 2,603)
  • Heap Fetches: 0
18. 0.067 0.498 ↑ 1.0 474 1

Hash (cost=138.36..138.36 rows=474 width=8) (actual time=0.498..0.498 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
19. 0.431 0.431 ↑ 1.0 474 1

Seq Scan on properties p_1 (cost=0.00..138.36 rows=474 width=8) (actual time=0.006..0.431 rows=474 loops=1)

  • Filter: ((property_type_id <> 99) AND (cid = 2,603) AND (is_disabled = 0))
  • Rows Removed by Filter: 69
20. 0.024 10.418 ↑ 1.0 1 2

Aggregate (cost=10.90..10.93 rows=1 width=96) (actual time=5.208..5.209 rows=1 loops=2)

  • Filter: (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END) IS NOT NULL)
21. 0.004 10.394 ↓ 6.0 6 2

Nested Loop (cost=2.62..10.87 rows=1 width=38) (actual time=5.183..5.197 rows=6 loops=2)

22. 0.024 10.366 ↓ 6.0 6 2

Hash Semi Join (cost=2.35..8.80 rows=1 width=25) (actual time=5.176..5.183 rows=6 loops=2)

  • Hash Cond: (gd.property_id = lp.property_id)
23. 0.040 0.040 ↓ 1.5 6 2

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..6.88 rows=4 width=21) (actual time=0.016..0.020 rows=6 loops=2)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((cid = 2,603) AND (gl_transaction_type_id = gh.gl_transaction_type_id))
24. 0.055 10.302 ↓ 468.0 468 1

Hash (cost=1.88..1.88 rows=1 width=8) (actual time=10.302..10.302 rows=468 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
25. 10.247 10.247 ↓ 468.0 468 1

Function Scan on load_properties lp (cost=0.25..1.88 rows=1 width=8) (actual time=10.196..10.247 rows=468 loops=1)

  • Filter: (cid = 2,603)
26. 0.024 0.024 ↑ 1.0 1 12

Index Scan using idx_properties_id on properties p (cost=0.28..2.07 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=12)

  • Index Cond: (id = gd.property_id)
  • Filter: ((cid = 2,603) AND (is_disabled = 0))
Planning time : 4.580 ms
Execution time : 8,643.644 ms