explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ML8

Settings
# exclusive inclusive rows x rows loops node
1. 146.686 313.384 ↓ 0.0 0 1

Insert on cached_gl_account_days (cost=71,678.11..72,419.89 rows=12,363 width=271) (actual time=313.384..313.384 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uk_cached_gl_account_days_all_ids_post_month_post_date
  • Tuples Inserted: 15033
  • Conflicting Tuples: 0
  • Buffers: shared hit=173224, local hit=279768 read=822 dirtied=821
2. 6.371 166.698 ↓ 1.2 15,033 1

Subquery Scan on *SELECT* (cost=71,678.11..72,419.89 rows=12,363 width=271) (actual time=144.620..166.698 rows=15,033 loops=1)

  • Buffers: shared hit=173224
3. 54.792 160.327 ↓ 1.2 15,033 1

HashAggregate (cost=71,678.11..71,925.37 rows=12,363 width=149) (actual time=144.616..160.327 rows=15,033 loops=1)

  • Group Key: gd.cid, gd.property_id, gd.accrual_gl_account_id, gd.period_id, gh.gl_book_id, COALESCE(gd.property_building_id, 0), COALESCE(gd.property_unit_id, 0), COALESCE(gd.gl_dimension_id, 0), COALESCE(gd.company_department_id, 0), gd.post_month, gh.post_date, (gh.gl_header_status_type_id = 2)
  • Buffers: shared hit=173224
4. 6.145 105.535 ↓ 3.4 41,730 1

Nested Loop (cost=1.00..71,152.68 rows=12,363 width=50) (actual time=0.093..105.535 rows=41,730 loops=1)

  • Buffers: shared hit=173224
5. 15.930 15.930 ↓ 2.0 41,730 1

Index Scan using idx_gl_details_cid_property_id_post_month_accrual_gl_account_id on gl_details gd (cost=0.56..21,509.79 rows=21,261 width=45) (actual time=0.060..15.930 rows=41,730 loops=1)

  • Index Cond: ((cid = 13576) AND (property_id = 347830) AND (post_month > '11/01/2019'::date) AND (accrual_gl_account_id IS NOT NULL))
  • Buffers: shared hit=6180
6. 83.460 83.460 ↑ 1.0 1 41,730

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..2.33 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=41,730)

  • Index Cond: (id = gd.gl_header_id)
  • Filter: ((is_template IS FALSE) AND (cid = 13576) AND (gl_header_status_type_id = ANY ('{1,2,3}'::integer[])))
  • Buffers: shared hit=167044
Planning time : 3.360 ms
Execution time : 314.549 ms