explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nUId

Settings
# exclusive inclusive rows x rows loops node
1. 36.357 166.531 ↓ 0.0 0 1

Insert on cached_gl_account_days (cost=71,610.73..72,321.60 rows=12,363 width=229) (actual time=166.531..166.531 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uk_cached_gl_account_days_all_ids_post_month_post_date
  • Tuples Inserted: 795
  • Conflicting Tuples: 321
2. 0.983 130.174 ↑ 11.1 1,116 1

Subquery Scan on "*SELECT*" (cost=71,610.73..72,321.60 rows=12,363 width=229) (actual time=128.095..130.174 rows=1,116 loops=1)

3. 26.277 129.191 ↑ 11.1 1,116 1

HashAggregate (cost=71,610.73..71,857.99 rows=12,363 width=133) (actual time=127.984..129.191 rows=1,116 loops=1)

  • Group Key: gd.cid, gd.property_id, gd.accrual_gl_account_id, gd.period_id, gh.gl_book_id, gd.post_month, gh.post_date, (gh.gl_header_status_type_id = 2)
4. 2.940 102.914 ↓ 3.4 41,730 1

Nested Loop (cost=1.00..71,208.93 rows=12,363 width=34) (actual time=0.094..102.914 rows=41,730 loops=1)

5. 16.514 16.514 ↓ 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,566.04 rows=21,261 width=29) (actual time=0.062..16.514 rows=41,730 loops=1)

  • Index Cond: ((cid = 13,576) AND (property_id = 347,830) AND (post_month > '2019-11-01'::date) AND (post_month <= '2020-01-10'::date) AND (accrual_gl_account_id IS NOT NULL))
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 = 13,576) AND (gl_header_status_type_id = ANY ('{1,3,2}'::integer[])))
Planning time : 3.966 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fkey_cached_gl_account_days_period_id 7.745 ms 795 0.010 ms
for constraint fkey_cached_gl_account_days_created_by 8.863 ms 795 0.011 ms
for constraint fkey_cached_gl_account_days_updated_by 6.386 ms 795 0.008 ms
for constraint fkey_cached_gl_account_days_updated_by 2.710 ms 317 0.009 ms
for constraint fkey_cached_gl_account_days_property_id 6.934 ms 795 0.009 ms
for constraint fkey_cached_gl_account_days_gl_account_id 6.704 ms 795 0.008 ms
for constraint fkey_cached_gl_account_days_gl_book_id 5.985 ms 795 0.008 ms