explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OU35

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 23,229.168 ↑ 1.0 50 1

Limit (cost=2,719,594.95..2,719,595.07 rows=50 width=44) (actual time=23,229.159..23,229.168 rows=50 loops=1)

2.          

CTE tb_max

3. 1.184 23,150.332 ↓ 1.3 5,980 1

Unique (cost=2,710,259.05..2,710,292.62 rows=4,476 width=12) (actual time=23,148.744..23,150.332 rows=5,980 loops=1)

4. 1.568 23,149.148 ↓ 1.3 5,980 1

Sort (cost=2,710,259.05..2,710,270.24 rows=4,476 width=12) (actual time=23,148.742..23,149.148 rows=5,980 loops=1)

  • Sort Key: tb_1.location_id, (max(tb_1.start_date))
  • Sort Method: quicksort Memory: 473kB
5. 3.402 23,147.580 ↓ 1.3 5,980 1

Finalize GroupAggregate (cost=2,709,875.73..2,709,987.63 rows=4,476 width=12) (actual time=23,143.068..23,147.580 rows=5,980 loops=1)

  • Group Key: tb_1.location_id
6. 6.167 23,144.178 ↓ 1.8 16,561 1

Sort (cost=2,709,875.73..2,709,898.11 rows=8,952 width=12) (actual time=23,143.058..23,144.178 rows=16,561 loops=1)

  • Sort Key: tb_1.location_id
  • Sort Method: quicksort Memory: 1545kB
7. 16.311 23,138.011 ↓ 1.8 16,561 1

Gather (cost=2,708,348.16..2,709,288.12 rows=8,952 width=12) (actual time=23,125.216..23,138.011 rows=16,561 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 2,750.015 23,121.700 ↓ 1.2 5,520 3

Partial HashAggregate (cost=2,707,348.16..2,707,392.92 rows=4,476 width=12) (actual time=23,120.554..23,121.700 rows=5,520 loops=3)

  • Group Key: tb_1.location_id
9. 3,404.496 20,371.685 ↓ 2.3 12,387,299 3

Hash Join (cost=56,398.15..2,680,329.81 rows=5,403,669 width=12) (actual time=890.979..20,371.685 rows=12,387,299 loops=3)

  • Hash Cond: (tb_1.schedule_id = sch.id)
10. 16,087.138 16,087.138 ↓ 1.5 12,388,102 3

Parallel Seq Scan on timeblock tb_1 (cost=0.00..2,522,503.34 rows=8,033,931 width=20) (actual time=0.076..16,087.138 rows=12,388,102 loops=3)

  • Filter: ((date_published IS NOT NULL) AND (deleted = 'N'::bpchar) AND (deleted_flag = 'N'::bpchar))
  • Rows Removed by Filter: 15742001
11. 261.015 880.051 ↓ 1.0 932,179 3

Hash (cost=45,233.65..45,233.65 rows=893,160 width=8) (actual time=880.051..880.051 rows=932,179 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 44606kB
12. 619.036 619.036 ↓ 1.0 932,179 3

Seq Scan on schedule sch (cost=0.00..45,233.65 rows=893,160 width=8) (actual time=0.071..619.036 rows=932,179 loops=3)

  • Filter: ((is_template = 'N'::bpchar) AND (deleted = 'N'::bpchar))
  • Rows Removed by Filter: 395709
13. 2.881 23,229.158 ↑ 112.8 100 1

Sort (cost=9,302.20..9,330.39 rows=11,277 width=44) (actual time=23,229.152..23,229.158 rows=100 loops=1)

  • Sort Key: tb.start_date_max
  • Sort Method: top-N heapsort Memory: 33kB
14. 15.214 23,226.277 ↓ 1.7 19,615 1

HashAggregate (cost=8,702.05..8,871.20 rows=11,277 width=44) (actual time=23,218.275..23,226.277 rows=19,615 loops=1)

  • Group Key: tb.start_date_max, location1.corporation_id
15. 5.150 23,211.063 ↓ 2.0 22,760 1

Hash Right Join (cost=8,535.50..8,645.66 rows=11,277 width=12) (actual time=23,203.068..23,211.063 rows=22,760 loops=1)

  • Hash Cond: (tb.location_id = location1.id)
16. 23,151.692 23,151.692 ↓ 1.3 5,980 1

CTE Scan on tb_max tb (cost=0.00..89.52 rows=4,476 width=12) (actual time=23,148.753..23,151.692 rows=5,980 loops=1)

17. 5.067 54.221 ↓ 2.0 22,760 1

Hash (cost=8,394.53..8,394.53 rows=11,277 width=16) (actual time=54.221..54.221 rows=22,760 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1323kB
18. 10.561 49.154 ↓ 2.0 22,760 1

Hash Join (cost=3,091.82..8,394.53 rows=11,277 width=16) (actual time=23.594..49.154 rows=22,760 loops=1)

  • Hash Cond: (location1.corporation_id = c.id)
19. 15.487 17.450 ↑ 1.0 22,850 1

Bitmap Heap Scan on location location1 (cost=186.52..5,197.43 rows=23,113 width=16) (actual time=2.291..17.450 rows=22,850 loops=1)

  • Recheck Cond: (deleted = 'N'::bpchar)
  • Heap Blocks: exact=2659
20. 1.963 1.963 ↑ 1.0 22,850 1

Bitmap Index Scan on idx_location_id_del (cost=0.00..180.74 rows=23,113 width=0) (actual time=1.963..1.963 rows=22,850 loops=1)

21. 4.273 21.143 ↓ 1.0 24,164 1

Hash (cost=2,604.15..2,604.15 rows=24,092 width=8) (actual time=21.143..21.143 rows=24,164 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1200kB
22. 14.411 16.870 ↓ 1.0 24,164 1

Bitmap Heap Scan on corporation c (cost=254.00..2,604.15 rows=24,092 width=8) (actual time=2.654..16.870 rows=24,164 loops=1)

  • Recheck Cond: (deleted = 'N'::bpchar)
  • Heap Blocks: exact=1710
23. 2.459 2.459 ↓ 1.0 24,199 1

Bitmap Index Scan on corporation_del (cost=0.00..247.98 rows=24,092 width=0) (actual time=2.459..2.459 rows=24,199 loops=1)

  • Index Cond: (deleted = 'N'::bpchar)