explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kIWR : Optimization for: plan #dk4Q

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 273.658 13,876.750 ↓ 91.2 1,095 1

GroupAggregate (cost=266,682.10..266,682.34 rows=12 width=21) (actual time=13,482.415..13,876.750 rows=1,095 loops=1)

  • Group Key: g.day, at.label
2. 731.666 13,603.092 ↓ 198,601.2 2,383,215 1

Sort (cost=266,682.10..266,682.13 rows=12 width=17) (actual time=13,482.350..13,603.092 rows=2,383,215 loops=1)

  • Sort Key: g.day, at.label
  • Sort Method: quicksort Memory: 234,225kB
3. 6,687.710 12,871.426 ↓ 198,601.2 2,383,215 1

Nested Loop (cost=0.00..266,681.89 rows=12 width=17) (actual time=7.223..12,871.426 rows=2,383,215 loops=1)

  • Join Filter: (aa.alignment_type_id = at.id)
  • Rows Removed by Join Filter: 90,562,170
4. 0.110 0.110 ↑ 1.1 39 1

Seq Scan on stakeholder_alignmenttype at (cost=0.00..1.42 rows=42 width=9) (actual time=0.007..0.110 rows=39 loops=1)

5. 4,835.595 6,183.606 ↓ 198,601.2 2,383,215 39

Materialize (cost=0.00..266,672.94 rows=12 width=20) (actual time=0.179..158.554 rows=2,383,215 loops=39)

6. 1,137.407 1,348.011 ↓ 198,601.2 2,383,215 1

Nested Loop (cost=0.00..266,672.88 rows=12 width=20) (actual time=6.958..1,348.011 rows=2,383,215 loops=1)

  • Join Filter: (((g.day)::timestamp with time zone <@ aa.effective_dates) AND ((g.day)::timestamp with time zone <@ aa.effective_dates))
  • Rows Removed by Join Filter: 1,946,565
7. 0.154 0.154 ↑ 2.7 366 1

Function Scan on generate_series g (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.043..0.154 rows=366 loops=1)

8. 207.957 210.450 ↑ 1.0 11,830 366

Materialize (cost=0.00..517.45 rows=11,830 width=29) (actual time=0.000..0.575 rows=11,830 loops=366)

9. 2.493 2.493 ↑ 1.0 11,830 1

Seq Scan on stakeholder_alignmentaction aa (cost=0.00..458.30 rows=11,830 width=29) (actual time=0.002..2.493 rows=11,830 loops=1)

Planning time : 0.171 ms
Execution time : 13,919.579 ms