explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i91E : test

Settings
# exclusive inclusive rows x rows loops node
1. 343.570 1,251,332.593 ↓ 1,228.4 73,705 1

Nested Loop Left Join (cost=705,724.00..706,509.69 rows=60 width=60) (actual time=22,671.848..1,251,332.593 rows=73,705 loops=1)

  • Buffers: shared hit=434645 read=846977 dirtied=548076 written=69641
  • I/O Timings: read=1228893.017 write=1668.558
2.          

CTE head_groups

3. 630.563 23,249.274 ↓ 4,606.6 73,705 1

GroupAggregate (cost=705,722.73..705,723.13 rows=16 width=52) (actual time=22,458.637..23,249.274 rows=73,705 loops=1)

  • Group Key: gen_company_last_data_1.head_group_company_id
  • Buffers: shared read=548007 dirtied=548007 written=69641
  • I/O Timings: read=5976.374 write=1668.558
4. 270.824 22,618.711 ↓ 12,786.8 204,589 1

Sort (cost=705,722.73..705,722.77 rows=16 width=16) (actual time=22,458.547..22,618.711 rows=204,589 loops=1)

  • Sort Key: gen_company_last_data_1.head_group_company_id
  • Sort Method: quicksort Memory: 19840kB
  • Buffers: shared read=548007 dirtied=548007 written=69641
  • I/O Timings: read=5976.374 write=1668.558
5. 7.479 22,347.887 ↓ 12,786.8 204,589 1

Gather (cost=1,000.00..705,722.41 rows=16 width=16) (actual time=0.934..22,347.887 rows=204,589 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared read=548007 dirtied=548007 written=69641
  • I/O Timings: read=5976.374 write=1668.558
6. 22,340.408 22,340.408 ↓ 9,742.3 68,196 3

Parallel Seq Scan on gen_company_last_data gen_company_last_data_1 (cost=0.00..704,720.81 rows=7 width=16) (actual time=0.091..22,340.408 rows=68,196 loops=3)

  • Filter: ((created_by_user_group = 1) AND (head_group_company_id <> ALL ('{383331,383445,314052}'::integer[])))
  • Rows Removed by Filter: 7631815
  • Buffers: shared read=548007 dirtied=548007 written=69641
  • I/O Timings: read=5976.374 write=1668.558
7. 313.525 48,639.358 ↓ 4,606.6 73,705 1

Nested Loop Left Join (cost=0.44..135.64 rows=16 width=60) (actual time=22,498.633..48,639.358 rows=73,705 loops=1)

  • Buffers: shared hit=215473 read=627580 dirtied=548007 written=69641
  • I/O Timings: read=29655.235 write=1668.558
8. 23,413.543 23,413.543 ↓ 4,606.6 73,705 1

CTE Scan on head_groups (cost=0.00..0.32 rows=16 width=52) (actual time=22,458.643..23,413.543 rows=73,705 loops=1)

  • Buffers: shared read=548007 dirtied=548007 written=69641
  • I/O Timings: read=5976.374 write=1668.558
9. 24,912.290 24,912.290 ↑ 1.0 1 73,705

Index Scan using gen_company_last_data_pkey on gen_company_last_data (cost=0.44..8.46 rows=1 width=12) (actual time=0.338..0.338 rows=1 loops=73,705)

  • Index Cond: ((company_id = head_groups.company_id) AND (created_by_user_group = 1))
  • Buffers: shared hit=215473 read=79573
  • I/O Timings: read=23678.861
10. 1,202,349.665 1,202,349.665 ↑ 4.0 1 73,705

Index Scan using establishment_company_id on establishment (cost=0.44..40.63 rows=4 width=12) (actual time=10.219..16.313 rows=1 loops=73,705)

  • Index Cond: (company_id = head_groups.company_id)
  • Filter: ((deleted_at IS NULL) AND record_visible AND (ref_establishment_type_id = 1) AND (ref_status_id = 1))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=219172 read=219397 dirtied=69
  • I/O Timings: read=1199237.782
Planning time : 1.365 ms
Execution time : 1,252,328.001 ms