explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 13Ge : Optimization for: Optimization for: Optimization for: plan #pme6; plan #kU7kn; plan #dVY2

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 825.035 20,350.563 ↑ 1,666.7 24 1

GroupAggregate (cost=1,653,769,143.29..1,653,875,795.29 rows=40,000 width=76) (actual time=19,323.208..20,350.563 rows=24 loops=1)

  • Group Key: a.date, ((a.doc ->> 'health'::text))
2. 978.762 19,525.528 ↑ 2.9 1,800,000 1

Sort (cost=1,653,769,143.29..1,653,782,399.79 rows=5,302,600 width=68) (actual time=19,261.587..19,525.528 rows=1,800,000 loops=1)

  • Sort Key: a.date, ((a.doc ->> 'health'::text))
  • Sort Method: external merge Disk: 133720kB
3. 364.878 18,546.766 ↑ 2.9 1,800,000 1

Subquery Scan on a (cost=1,650,614,475.27..1,652,741,908.77 rows=5,302,600 width=68) (actual time=12,389.556..18,546.766 rows=1,800,000 loops=1)

4. 4,564.685 18,181.888 ↑ 2.9 1,800,000 1

GroupAggregate (cost=1,650,614,475.27..1,652,675,626.27 rows=5,302,600 width=44) (actual time=12,389.554..18,181.888 rows=1,800,000 loops=1)

  • Group Key: days.date, es.entity_id
5. 9,615.923 13,617.203 ↑ 37.2 3,600,000 1

Sort (cost=1,650,614,475.27..1,650,949,162.77 rows=133,875,000 width=45) (actual time=12,389.529..13,617.203 rows=3,600,000 loops=1)

  • Sort Key: days.date, es.entity_id
  • Sort Method: external merge Disk: 205248kB
6. 478.596 4,001.280 ↑ 37.2 3,600,000 1

Nested Loop Left Join (cost=0.42..1,620,189,141.38 rows=133,875,000 width=45) (actual time=0.360..4,001.280 rows=3,600,000 loops=1)

7. 0.024 0.024 ↑ 212.5 12 1

Seq Scan on days (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.011..0.024 rows=12 loops=1)

8. 3,522.660 3,522.660 ↓ 1.1 300,000 12

Index Scan using event_series_created_created_at on event_series es (cost=0.42..632,743.28 rows=262,500 width=63) (actual time=0.173..293.555 rows=300,000 loops=12)

  • Index Cond: ((days.date)::timestamp with time zone <@ valid)
Planning time : 0.281 ms
Execution time : 20,383.728 ms