explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eqYs

Settings
# exclusive inclusive rows x rows loops node
1. 34.143 443,847.649 ↑ 129.9 45,459 1

Append (cost=1,696,505.67..16,526,145.77 rows=5,904,907 width=125) (actual time=13,670.940..443,847.649 rows=45,459 loops=1)

2. 10.486 13,699.446 ↑ 67.4 12,172 1

Subquery Scan on "*SELECT* 1" (cost=1,696,505.67..1,719,056.55 rows=820,032 width=125) (actual time=13,670.938..13,699.446 rows=12,172 loops=1)

3. 1,082.765 13,688.960 ↑ 67.4 12,172 1

HashAggregate (cost=1,696,505.67..1,708,806.15 rows=820,032 width=124) (actual time=13,670.936..13,688.960 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,373.711 12,606.195 ↓ 1.1 942,197 1

Nested Loop Left Join (cost=0.29..1,661,048.19 rows=886,437 width=61) (actual time=1,585.884..12,606.195 rows=942,197 loops=1)

5. 10,290.287 10,290.287 ↓ 1.1 942,197 1

Seq Scan on gl_detail_temp gd (cost=0.00..1,649,959.42 rows=886,437 width=57) (actual time=1,585.854..10,290.287 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.174 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.023 0.023 ↑ 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.021..0.023 rows=1 loops=1)

  • Index Cond: ((cid = 13,531) AND (id = 6,458))
8. 30.740 353,547.926 ↑ 153.6 33,110 1

Subquery Scan on "*SELECT* 2" (cost=10,258,632.82..12,742,589.68 rows=5,084,722 width=125) (actual time=279,518.936..353,547.926 rows=33,110 loops=1)

9. 40,766.574 353,517.186 ↑ 153.6 33,110 1

GroupAggregate (cost=10,258,632.82..12,666,318.85 rows=5,084,722 width=96) (actual time=279,518.933..353,517.186 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. 91,468.365 312,750.612 ↑ 1.0 46,639,762 1

Sort (cost=10,258,632.82..10,375,839.17 rows=46,882,540 width=65) (actual time=279,518.754..312,750.612 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. 61,561.286 221,282.247 ↑ 1.0 46,639,762 1

Nested Loop Left Join (cost=236.97..2,362,282.58 rows=46,882,540 width=65) (actual time=1.707..221,282.247 rows=46,639,762 loops=1)

12. 40,780.076 113,081.199 ↑ 1.0 46,639,762 1

Hash Join (cost=236.69..1,776,242.52 rows=46,882,540 width=61) (actual time=1.690..113,081.199 rows=46,639,762 loops=1)

  • Hash Cond: (gat.gl_branch_id = gb.id)
13. 42,404.239 72,300.577 ↑ 1.0 46,639,762 1

Hash Join (cost=207.09..1,652,111.40 rows=46,882,540 width=65) (actual time=1.134..72,300.577 rows=46,639,762 loops=1)

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

Seq Scan on gl_detail_temp gd_1 (cost=0.00..1,522,841.36 rows=48,895,401 width=57) (actual time=0.012..29,895.224 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 1.114 ↑ 1.0 732 1

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

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

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

  • Recheck Cond: (gl_tree_id = 2,708)
  • Filter: (cid = 13,531)
  • Heap Blocks: exact=121
17. 0.046 0.046 ↑ 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.046 rows=732 loops=1)

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

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

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

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

  • Filter: (cid = 13,531)
20. 46,639.749 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.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

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

Subquery Scan on "*SELECT* 3" (cost=2,064,491.51..2,064,499.55 rows=153 width=125) (actual time=76,565.640..76,566.134 rows=177 loops=1)

23. 0.278 76,565.975 ↓ 1.2 177 1

GroupAggregate (cost=2,064,491.51..2,064,496.49 rows=153 width=92) (actual time=76,565.638..76,565.975 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.201 76,565.697 ↓ 1.2 177 1

Sort (cost=2,064,491.51..2,064,491.90 rows=153 width=107) (actual time=76,565.628..76,565.697 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,565.496 ↓ 1.2 177 1

Hash Join (cost=2,064,475.27..2,064,485.96 rows=153 width=107) (actual time=76,564.859..76,565.496 rows=177 loops=1)

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

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

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

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

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

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

29. 0.122 0.289 ↑ 1.0 250 1

Hash (cost=15.12..15.12 rows=250 width=12) (actual time=0.288..0.289 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.336 0.753 ↑ 1.0 732 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
32. 0.417 0.417 ↑ 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.417 rows=732 loops=1)

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

Hash (cost=2,064,423.98..2,064,423.98 rows=153 width=72) (actual time=76,563.779..76,563.779 rows=177 loops=1)

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

Subquery Scan on prior_year_income_expenses (cost=2,064,420.16..2,064,423.98 rows=153 width=72) (actual time=76,563.395..76,563.684 rows=177 loops=1)

35. 6,441.968 76,563.549 ↓ 1.2 177 1

HashAggregate (cost=2,064,420.16..2,064,422.45 rows=153 width=72) (actual time=76,563.394..76,563.549 rows=177 loops=1)

  • Group Key: gd_2.cid, gd_2.property_id
36. 17,314.471 70,121.581 ↑ 2.8 8,493,457 1

Hash Join (cost=236.02..1,707,897.71 rows=23,768,163 width=13) (actual time=1.431..70,121.581 rows=8,493,457 loops=1)

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

Hash Join (cost=207.09..1,617,269.63 rows=34,226,155 width=21) (actual time=1.025..52,806.710 rows=33,902,765 loops=1)

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

Seq Scan on gl_detail_temp gd_2 (cost=0.00..1,522,841.36 rows=35,695,625 width=17) (actual time=0.010..23,345.872 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.359 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.604 0.649 ↑ 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.062..0.649 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.163 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.237 0.237 ↑ 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.237 rows=350 loops=1)

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