explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YwGX

Settings
# exclusive inclusive rows x rows loops node
1. 11,708.059 60,993.873 ↑ 5.2 88,695 1

HashAggregate (cost=1,634,052.19..1,645,681.09 rows=465,156 width=208) (actual time=60,808.552..60,993.873 rows=88,695 loops=1)

  • Group Key: "*SELECT* 1".cid, "*SELECT* 1".property_id, "*SELECT* 1".gl_account_id, "*SELECT* 1".post_month
2. 7,458.819 49,285.814 ↓ 2.1 9,754,217 1

Append (cost=263,696.81..1,424,732.03 rows=4,651,559 width=80) (actual time=7,959.026..49,285.814 rows=9,754,217 loops=1)

3. 2,983.298 20,200.507 ↓ 2.0 3,414,408 1

Subquery Scan on *SELECT* 1 (cost=263,696.81..697,173.72 rows=1,683,235 width=53) (actual time=7,959.025..20,200.507 rows=3,414,408 loops=1)

4. 3,754.709 17,217.209 ↓ 2.0 3,414,408 1

Hash Join (cost=263,696.81..676,133.28 rows=1,683,235 width=25) (actual time=7,959.017..17,217.209 rows=3,414,408 loops=1)

  • Hash Cond: ((gd.cid = gh.cid) AND (gd.gl_header_id = gh.id))
5. 5,528.821 5,528.821 ↑ 1.0 3,418,367 1

Seq Scan on gl_details gd (cost=0.00..393,678.59 rows=3,572,931 width=25) (actual time=1.659..5,528.821 rows=3,418,367 loops=1)

  • Filter: ((cash_gl_account_id IS NOT NULL) AND (post_month > '2018-01-01'::date))
  • Rows Removed by Filter: 10876894
6. 2,196.853 7,933.679 ↓ 1.0 3,226,893 1

Hash (cost=215,504.57..215,504.57 rows=3,212,816 width=8) (actual time=7,933.679..7,933.679 rows=3,226,893 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 158819kB
7. 5,736.826 5,736.826 ↓ 1.0 3,226,893 1

Index Scan using idx_gl_headers_post_month on gl_headers gh (cost=0.43..215,504.57 rows=3,212,816 width=8) (actual time=1.700..5,736.826 rows=3,226,893 loops=1)

  • Index Cond: (post_month > '2018-01-01'::date)
  • Filter: (gl_header_status_type_id = ANY ('{1,3}'::integer[]))
  • Rows Removed by Filter: 335
8. 5,489.609 21,626.488 ↓ 2.1 6,339,809 1

Subquery Scan on *SELECT* 2 (cost=263,696.81..727,558.31 rows=2,968,324 width=53) (actual time=4,175.678..21,626.488 rows=6,339,809 loops=1)

9. 6,785.982 16,136.879 ↓ 2.1 6,339,809 1

Hash Join (cost=263,696.81..690,454.26 rows=2,968,324 width=25) (actual time=4,175.673..16,136.879 rows=6,339,809 loops=1)

  • Hash Cond: ((gd_1.cid = gh_1.cid) AND (gd_1.gl_header_id = gh_1.id))
10. 5,193.286 5,193.286 ↓ 1.0 6,343,991 1

Seq Scan on gl_details gd_1 (cost=0.00..393,678.59 rows=6,300,737 width=25) (actual time=0.007..5,193.286 rows=6,343,991 loops=1)

  • Filter: ((accrual_gl_account_id IS NOT NULL) AND (post_month > '2018-01-01'::date))
  • Rows Removed by Filter: 7951270
11. 1,996.443 4,157.611 ↓ 1.0 3,226,893 1

Hash (cost=215,504.57..215,504.57 rows=3,212,816 width=8) (actual time=4,157.611..4,157.611 rows=3,226,893 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 158819kB
12. 2,161.168 2,161.168 ↓ 1.0 3,226,893 1

Index Scan using idx_gl_headers_post_month on gl_headers gh_1 (cost=0.43..215,504.57 rows=3,212,816 width=8) (actual time=0.028..2,161.168 rows=3,226,893 loops=1)

  • Index Cond: (post_month > '2018-01-01'::date)
  • Filter: (gl_header_status_type_id = ANY ('{1,3}'::integer[]))
  • Rows Removed by Filter: 335