explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gwLr

Settings
# exclusive inclusive rows x rows loops node
1. 4.149 146,435.454 ↑ 189.4 49,232 1

Append (cost=1,972,001.57..15,871,044.87 rows=9,322,560 width=125) (actual time=12,367.358..146,435.454 rows=49,232 loops=1)

2. 2.974 17,351.589 ↑ 248.5 17,056 1

Subquery Scan on *SELECT* 1 (cost=1,972,001.57..2,505,659.47 rows=4,237,600 width=125) (actual time=12,367.357..17,351.589 rows=17,056 loops=1)

3. 3,375.519 17,348.615 ↑ 248.5 17,056 1

GroupAggregate (cost=1,972,001.57..2,452,689.47 rows=4,237,600 width=124) (actual time=12,367.355..17,348.615 rows=17,056 loops=1)

  • Group Key: gd.cid, gd.property_id, gd.property_name, gd.lookup_code, gd.accrual_gl_account_id
4. 9,456.246 13,973.096 ↑ 1.0 8,184,524 1

Sort (cost=1,972,001.57..1,992,857.77 rows=8,342,478 width=65) (actual time=12,367.328..13,973.096 rows=8,184,524 loops=1)

  • Sort Key: gd.property_id, gd.property_name, gd.lookup_code, gd.accrual_gl_account_id
  • Sort Method: external merge Disk: 657,944kB
5. 2,469.960 4,516.850 ↑ 1.0 8,184,524 1

Nested Loop Left Join (cost=2,139.83..1,012,948.44 rows=8,342,478 width=65) (actual time=6.073..4,516.850 rows=8,184,524 loops=1)

6. 2,042.086 2,046.890 ↑ 1.0 8,184,524 1

Bitmap Heap Scan on gl_detail_temp gd (cost=2,139.54..908,664.96 rows=8,342,478 width=61) (actual time=6.060..2,046.890 rows=8,184,524 loops=1)

  • Recheck Cond: ((post_month >= '01/01/2020'::date) AND (post_month <= '07/01/2020'::date))
  • Rows Removed by Index Recheck: 270,803
  • Filter: (cid = 13,531)
  • Heap Blocks: lossy=125,384
7. 4.804 4.804 ↑ 6.7 1,254,400 1

Bitmap Index Scan on idx_temp_gl_detail_id_temp (cost=0.00..53.92 rows=8,350,481 width=0) (actual time=4.804..4.804 rows=1,254,400 loops=1)

  • Index Cond: ((post_month >= '01/01/2020'::date) AND (post_month <= '07/01/2020'::date))
8. 0.000 0.000 ↑ 1.0 1 8,184,524

Materialize (cost=0.29..2.51 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=8,184,524)

9. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_company_users on company_users cu (cost=0.29..2.51 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: ((cid = 13,531) AND (id = 6,458))
10. 7.681 104,180.704 ↑ 159.0 31,987 1

Subquery Scan on *SELECT* 2 (cost=8,789,150.93..11,226,654.21 rows=5,084,810 width=125) (actual time=75,606.999..104,180.704 rows=31,987 loops=1)

11. 15,621.907 104,173.023 ↑ 159.0 31,987 1

GroupAggregate (cost=8,789,150.93..11,150,382.06 rows=5,084,810 width=96) (actual time=75,606.997..104,173.023 rows=31,987 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
12. 48,949.945 88,551.116 ↑ 1.1 40,749,087 1

Sort (cost=8,789,150.93..8,898,563.84 rows=43,765,162 width=65) (actual time=75,606.908..88,551.116 rows=40,749,087 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,162,128kB
13. 12,346.114 39,601.171 ↑ 1.1 40,749,087 1

Nested Loop Left Join (cost=10,940.77..2,183,244.57 rows=43,765,162 width=65) (actual time=25.056..39,601.171 rows=40,749,087 loops=1)

14. 7,595.877 27,255.057 ↑ 1.1 40,749,087 1

Hash Join (cost=10,940.48..1,636,177.53 rows=43,765,162 width=61) (actual time=25.041..27,255.057 rows=40,749,087 loops=1)

  • Hash Cond: (gat.gl_branch_id = gb.id)
15. 8,634.855 19,659.005 ↑ 1.1 40,749,087 1

Hash Join (cost=10,910.88..1,520,298.33 rows=43,765,162 width=65) (actual time=24.857..19,659.005 rows=40,749,087 loops=1)

  • Hash Cond: (gd_1.accrual_gl_account_id = gat.gl_account_id)
16. 10,999.614 11,023.644 ↓ 1.0 42,600,585 1

Bitmap Heap Scan on gl_detail_temp gd_1 (cost=10,789.21..1,408,130.20 rows=42,430,979 width=57) (actual time=24.343..11,023.644 rows=42,600,585 loops=1)

  • Recheck Cond: (post_month < '01/01/2020'::date)
  • Rows Removed by Index Recheck: 794,243
  • Filter: (cid = 13,531)
  • Heap Blocks: lossy=649,984
17. 24.030 24.030 ↑ 6.5 6,499,840 1

Bitmap Index Scan on idx_temp_gl_detail_id_temp (cost=0.00..181.46 rows=42,463,266 width=0) (actual time=24.030..24.030 rows=6,499,840 loops=1)

  • Index Cond: (post_month < '01/01/2020'::date)
18. 0.091 0.506 ↑ 1.0 732 1

Hash (cost=112.52..112.52 rows=732 width=12) (actual time=0.506..0.506 rows=732 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
19. 0.415 0.415 ↑ 1.0 732 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.28..112.52 rows=732 width=12) (actual time=0.020..0.415 rows=732 loops=1)

  • Index Cond: ((cid = 13,531) AND (gl_tree_id = 2,708))
20. 0.062 0.175 ↑ 1.0 504 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
21. 0.113 0.113 ↑ 1.0 504 1

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

  • Filter: (cid = 13,531)
22. 0.000 0.000 ↑ 1.0 1 40,749,087

Materialize (cost=0.29..2.51 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=40,749,087)

23. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: ((cid = 13,531) AND (id = 6,458))
24. 0.040 24,899.012 ↓ 1.3 189 1

Subquery Scan on *SELECT* 3 (cost=2,092,110.51..2,092,118.39 rows=150 width=125) (actual time=24,898.835..24,899.012 rows=189 loops=1)

25. 0.137 24,898.972 ↓ 1.3 189 1

GroupAggregate (cost=2,092,110.51..2,092,115.39 rows=150 width=92) (actual time=24,898.833..24,898.972 rows=189 loops=1)

  • Group Key: pgs.cid, pgs.property_id, load_prop.property_name, load_prop.lookup_code, pgs.retained_earnings_gl_account_id
26. 0.087 24,898.835 ↓ 1.3 189 1

Sort (cost=2,092,110.51..2,092,110.89 rows=150 width=107) (actual time=24,898.825..24,898.835 rows=189 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: 50kB
27. 0.047 24,898.748 ↓ 1.3 189 1

Hash Join (cost=2,092,078.01..2,092,105.09 rows=150 width=107) (actual time=24,898.468..24,898.748 rows=189 loops=1)

  • Hash Cond: (load_prop.property_id = prior_year_income_expenses.property_id)
28. 0.066 0.384 ↑ 1.0 204 1

Hash Join (cost=8.40..33.22 rows=204 width=47) (actual time=0.146..0.384 rows=204 loops=1)

  • Hash Cond: (pgs.property_id = load_prop.property_id)
29. 0.075 0.241 ↑ 1.0 250 1

Merge Join (cost=0.81..22.66 rows=250 width=12) (actual time=0.058..0.241 rows=250 loops=1)

  • Merge Cond: (pgs.retained_earnings_gl_account_id = gat_1.gl_account_id)
30. 0.082 0.082 ↑ 1.0 250 1

Index Scan using idx_property_gl_settings_retained_earnings_gl_account_id on property_gl_settings pgs (cost=0.14..18.82 rows=250 width=12) (actual time=0.016..0.082 rows=250 loops=1)

  • Filter: (cid = 13,531)
31. 0.084 0.084 ↑ 2.8 261 1

Index Scan using idx_temp_gat_gl_account_id on gat_temp gat_1 (cost=0.28..30.41 rows=732 width=8) (actual time=0.036..0.084 rows=261 loops=1)

  • Filter: ((cid = 13,531) AND (gl_tree_id = 2,708))
32. 0.032 0.077 ↑ 1.0 204 1

Hash (cost=5.04..5.04 rows=204 width=35) (actual time=0.077..0.077 rows=204 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
33. 0.045 0.045 ↑ 1.0 204 1

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

34. 0.034 24,898.317 ↓ 1.3 189 1

Hash (cost=2,092,067.73..2,092,067.73 rows=150 width=72) (actual time=24,898.317..24,898.317 rows=189 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
35. 0.022 24,898.283 ↓ 1.3 189 1

Subquery Scan on prior_year_income_expenses (cost=2,092,063.98..2,092,067.73 rows=150 width=72) (actual time=24,898.174..24,898.283 rows=189 loops=1)

36. 3,242.912 24,898.261 ↓ 1.3 189 1

HashAggregate (cost=2,092,063.98..2,092,066.23 rows=150 width=72) (actual time=24,898.173..24,898.261 rows=189 loops=1)

  • Group Key: gd_2.cid, gd_2.property_id
37. 3,937.578 21,655.349 ↑ 3.0 10,247,599 1

Hash Join (cost=10,939.81..1,636,176.87 rows=30,392,474 width=13) (actual time=25.140..21,655.349 rows=10,247,599 loops=1)

  • Hash Cond: (gat_2.gl_branch_id = gb_1.id)
38. 7,641.726 17,717.622 ↑ 1.1 40,749,087 1

Hash Join (cost=10,910.88..1,520,298.33 rows=43,765,162 width=21) (actual time=24.987..17,717.622 rows=40,749,087 loops=1)

  • Hash Cond: (gd_2.accrual_gl_account_id = gat_2.gl_account_id)
39. 10,051.258 10,075.384 ↓ 1.0 42,600,585 1

Bitmap Heap Scan on gl_detail_temp gd_2 (cost=10,789.21..1,408,130.20 rows=42,430,979 width=17) (actual time=24.469..10,075.384 rows=42,600,585 loops=1)

  • Recheck Cond: (post_month < '01/01/2020'::date)
  • Rows Removed by Index Recheck: 794,243
  • Filter: (cid = 13,531)
  • Heap Blocks: lossy=649,984
40. 24.126 24.126 ↑ 6.5 6,499,840 1

Bitmap Index Scan on idx_temp_gl_detail_id_temp (cost=0.00..181.46 rows=42,463,266 width=0) (actual time=24.126..24.126 rows=6,499,840 loops=1)

  • Index Cond: (post_month < '01/01/2020'::date)
41. 0.113 0.512 ↑ 1.0 732 1

Hash (cost=112.52..112.52 rows=732 width=12) (actual time=0.512..0.512 rows=732 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
42. 0.399 0.399 ↑ 1.0 732 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat_2 (cost=0.28..112.52 rows=732 width=12) (actual time=0.017..0.399 rows=732 loops=1)

  • Index Cond: ((cid = 13,531) AND (gl_tree_id = 2,708))
43. 0.042 0.149 ↑ 1.0 350 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
44. 0.107 0.107 ↑ 1.0 350 1

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

  • Filter: ((gl_group_type_id = ANY ('{4,5}'::integer[])) AND (cid = 13,531))
  • Rows Removed by Filter: 154
Planning time : 2.407 ms
Execution time : 146,887.312 ms