explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iv5G

Settings
# exclusive inclusive rows x rows loops node
1. 33.195 438,190.209 ↑ 129.3 45,459 1

Append (cost=1,694,543.42..16,458,827.98 rows=5,876,525 width=125) (actual time=12,650.387..438,190.209 rows=45,459 loops=1)

2. 9.989 12,676.045 ↑ 65.2 12,172 1

Subquery Scan on "*SELECT* 1" (cost=1,694,543.42..1,716,367.75 rows=793,612 width=125) (actual time=12,650.386..12,676.045 rows=12,172 loops=1)

3. 993.431 12,666.056 ↑ 65.2 12,172 1

HashAggregate (cost=1,694,543.42..1,706,447.60 rows=793,612 width=124) (actual time=12,650.384..12,666.056 rows=12,172 loops=1)

  • Group Key: gd.cid, gd.property_id, gd.property_name, gd.lookup_code, gd.accrual_gl_account_id
4. 1,215.145 11,672.625 ↓ 1.1 942,197 1

Nested Loop Left Join (cost=0.29..1,660,319.49 rows=855,598 width=61) (actual time=1,420.642..11,672.625 rows=942,197 loops=1)

5. 9,515.283 9,515.283 ↓ 1.1 942,197 1

Seq Scan on gl_detail_temp gd (cost=0.00..1,649,616.21 rows=855,598 width=57) (actual time=1,420.615..9,515.283 rows=942,197 loops=1)

  • Filter: ((post_month >= '2020-06-01'::date) AND (post_month <= '2020-06-01'::date) AND (cid = 13,531))
  • Rows Removed by Filter: 49,899,885
6. 942.178 942.197 ↑ 1.0 1 942,197

Materialize (cost=0.29..8.31 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=942,197)

7. 0.019 0.019 ↑ 1.0 1 1

Index Scan using pk_company_users on company_users cu (cost=0.29..8.31 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: ((cid = 13,531) AND (id = 6,458))
8. 30.531 349,255.757 ↑ 153.5 33,110 1

Subquery Scan on "*SELECT* 2" (cost=10,209,764.36..12,680,995.24 rows=5,082,761 width=125) (actual time=274,996.297..349,255.757 rows=33,110 loops=1)

9. 40,871.245 349,225.226 ↑ 153.5 33,110 1

GroupAggregate (cost=10,209,764.36..12,604,753.82 rows=5,082,761 width=96) (actual time=274,996.294..349,225.226 rows=33,110 loops=1)

  • Group Key: gd_1.cid, gd_1.property_id, gd_1.property_name, gd_1.lookup_code, gd_1.accrual_gl_account_id
10. 89,674.615 308,353.981 ↓ 1.0 46,639,762 1

Sort (cost=10,209,764.36..10,326,337.11 rows=46,629,099 width=65) (actual time=274,996.112..308,353.981 rows=46,639,762 loops=1)

  • Sort Key: gd_1.property_id, gd_1.property_name, gd_1.lookup_code, gd_1.accrual_gl_account_id
  • Sort Method: external merge Disk: 3,612,024kB
11. 60,317.482 218,679.366 ↓ 1.0 46,639,762 1

Nested Loop Left Join (cost=236.97..2,357,923.97 rows=46,629,099 width=65) (actual time=1.551..218,679.366 rows=46,639,762 loops=1)

12. 40,350.637 111,722.122 ↓ 1.0 46,639,762 1

Hash Join (cost=236.69..1,775,051.93 rows=46,629,099 width=61) (actual time=1.535..111,722.122 rows=46,639,762 loops=1)

  • Hash Cond: (gat.gl_branch_id = gb.id)
13. 41,865.071 71,370.965 ↓ 1.0 46,639,762 1

Hash Join (cost=207.09..1,651,591.68 rows=46,629,099 width=65) (actual time=1.005..71,370.965 rows=46,639,762 loops=1)

  • Hash Cond: (gd_1.accrual_gl_account_id = gat.gl_account_id)
14. 29,504.906 29,504.906 ↑ 1.0 48,708,896 1

Seq Scan on gl_detail_temp gd_1 (cost=0.00..1,522,547.18 rows=48,810,948 width=57) (actual time=0.010..29,504.906 rows=48,708,896 loops=1)

  • Filter: ((post_month < '2020-06-01'::date) AND (cid = 13,531))
  • Rows Removed by Filter: 2,133,186
15. 0.352 0.988 ↑ 1.0 732 1

Hash (cost=197.94..197.94 rows=732 width=12) (actual time=0.987..0.988 rows=732 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
16. 0.592 0.636 ↑ 1.0 732 1

Bitmap Heap Scan on gl_account_trees gat (cost=17.96..197.94 rows=732 width=12) (actual time=0.062..0.636 rows=732 loops=1)

  • Recheck Cond: (gl_tree_id = 2,708)
  • Filter: (cid = 13,531)
  • Heap Blocks: exact=121
17. 0.044 0.044 ↑ 1.0 732 1

Bitmap Index Scan on idx_gl_account_trees_gl_tree_id (cost=0.00..17.77 rows=732 width=0) (actual time=0.044..0.044 rows=732 loops=1)

  • Index Cond: (gl_tree_id = 2,708)
18. 0.237 0.520 ↑ 1.0 504 1

Hash (cost=23.30..23.30 rows=504 width=12) (actual time=0.519..0.520 rows=504 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
19. 0.283 0.283 ↑ 1.0 504 1

Seq Scan on gl_branches gb (cost=0.00..23.30 rows=504 width=12) (actual time=0.010..0.283 rows=504 loops=1)

  • Filter: (cid = 13,531)
20. 46,639.750 46,639.762 ↑ 1.0 1 46,639,762

Materialize (cost=0.29..8.31 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=46,639,762)

21. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: ((cid = 13,531) AND (id = 6,458))
22. 0.158 76,225.212 ↓ 1.2 177 1

Subquery Scan on "*SELECT* 3" (cost=2,061,457.02..2,061,465.00 rows=152 width=125) (actual time=76,224.721..76,225.212 rows=177 loops=1)

23. 0.277 76,225.054 ↓ 1.2 177 1

GroupAggregate (cost=2,061,457.02..2,061,461.96 rows=152 width=92) (actual time=76,224.718..76,225.054 rows=177 loops=1)

  • Group Key: pgs.cid, pgs.property_id, load_prop.property_name, load_prop.lookup_code, pgs.retained_earnings_gl_account_id
24. 0.200 76,224.777 ↓ 1.2 177 1

Sort (cost=2,061,457.02..2,061,457.40 rows=152 width=107) (actual time=76,224.708..76,224.777 rows=177 loops=1)

  • Sort Key: pgs.property_id, load_prop.property_name, load_prop.lookup_code, pgs.retained_earnings_gl_account_id
  • Sort Method: quicksort Memory: 49kB
25. 0.176 76,224.577 ↓ 1.2 177 1

Hash Join (cost=2,061,440.83..2,061,451.51 rows=152 width=107) (actual time=76,223.941..76,224.577 rows=177 loops=1)

  • Hash Cond: (load_prop.property_id = prior_year_income_expenses.property_id)
26. 0.181 1.533 ↑ 1.0 204 1

Hash Join (cost=49.38..57.77 rows=204 width=47) (actual time=1.065..1.533 rows=204 loops=1)

  • Hash Cond: (pgs.retained_earnings_gl_account_id = gat_1.gl_account_id)
27. 0.213 0.607 ↑ 1.0 204 1

Hash Join (cost=18.25..23.84 rows=204 width=47) (actual time=0.313..0.607 rows=204 loops=1)

  • Hash Cond: (load_prop.property_id = pgs.property_id)
28. 0.106 0.106 ↑ 1.0 204 1

Seq Scan on load_prop (cost=0.00..5.04 rows=204 width=35) (actual time=0.014..0.106 rows=204 loops=1)

29. 0.121 0.288 ↑ 1.0 250 1

Hash (cost=15.12..15.12 rows=250 width=12) (actual time=0.288..0.288 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
30. 0.167 0.167 ↑ 1.0 250 1

Seq Scan on property_gl_settings pgs (cost=0.00..15.12 rows=250 width=12) (actual time=0.008..0.167 rows=250 loops=1)

  • Filter: (cid = 13,531)
31. 0.333 0.745 ↑ 1.0 732 1

Hash (cost=21.98..21.98 rows=732 width=8) (actual time=0.745..0.745 rows=732 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
32. 0.412 0.412 ↑ 1.0 732 1

Seq Scan on gat_temp gat_1 (cost=0.00..21.98 rows=732 width=8) (actual time=0.010..0.412 rows=732 loops=1)

  • Filter: ((cid = 13,531) AND (gl_tree_id = 2,708))
33. 0.095 76,222.868 ↓ 1.2 177 1

Hash (cost=2,061,389.55..2,061,389.55 rows=152 width=72) (actual time=76,222.868..76,222.868 rows=177 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
34. 0.134 76,222.773 ↓ 1.2 177 1

Subquery Scan on prior_year_income_expenses (cost=2,061,385.75..2,061,389.55 rows=152 width=72) (actual time=76,222.488..76,222.773 rows=177 loops=1)

35. 6,424.201 76,222.639 ↓ 1.2 177 1

HashAggregate (cost=2,061,385.75..2,061,388.03 rows=152 width=72) (actual time=76,222.486..76,222.639 rows=177 loops=1)

  • Group Key: gd_2.cid, gd_2.property_id
36. 17,252.462 69,798.438 ↑ 2.8 8,493,457 1

Hash Join (cost=236.02..1,706,894.94 rows=23,632,721 width=13) (actual time=1.432..69,798.438 rows=8,493,457 loops=1)

  • Hash Cond: (gat_2.gl_branch_id = gb_1.id)
37. 29,211.057 52,545.576 ↑ 1.0 33,902,765 1

Hash Join (cost=207.09..1,616,783.13 rows=34,031,118 width=21) (actual time=1.026..52,545.576 rows=33,902,765 loops=1)

  • Hash Cond: (gd_2.accrual_gl_account_id = gat_2.gl_account_id)
38. 23,333.511 23,333.511 ↑ 1.0 35,425,901 1

Seq Scan on gl_detail_temp gd_2 (cost=0.00..1,522,547.18 rows=35,623,488 width=17) (actual time=0.010..23,333.511 rows=35,425,901 loops=1)

  • Filter: ((post_month < '2019-08-01'::date) AND (cid = 13,531))
  • Rows Removed by Filter: 15,416,181
39. 0.364 1.008 ↑ 1.0 732 1

Hash (cost=197.94..197.94 rows=732 width=12) (actual time=1.008..1.008 rows=732 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
40. 0.599 0.644 ↑ 1.0 732 1

Bitmap Heap Scan on gl_account_trees gat_2 (cost=17.96..197.94 rows=732 width=12) (actual time=0.063..0.644 rows=732 loops=1)

  • Recheck Cond: (gl_tree_id = 2,708)
  • Filter: (cid = 13,531)
  • Heap Blocks: exact=121
41. 0.045 0.045 ↑ 1.0 732 1

Bitmap Index Scan on idx_gl_account_trees_gl_tree_id (cost=0.00..17.77 rows=732 width=0) (actual time=0.045..0.045 rows=732 loops=1)

  • Index Cond: (gl_tree_id = 2,708)
42. 0.162 0.400 ↑ 1.0 350 1

Hash (cost=24.56..24.56 rows=350 width=8) (actual time=0.400..0.400 rows=350 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
43. 0.238 0.238 ↑ 1.0 350 1

Seq Scan on gl_branches gb_1 (cost=0.00..24.56 rows=350 width=8) (actual time=0.008..0.238 rows=350 loops=1)

  • Filter: ((gl_group_type_id = ANY ('{4,5}'::integer[])) AND (cid = 13,531))
  • Rows Removed by Filter: 154