explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c0yq : 6 months

Settings
# exclusive inclusive rows x rows loops node
1. 266.650 82,357.670 ↑ 1.0 1 1

Aggregate (cost=161,431,335.47..161,431,335.48 rows=1 width=12) (actual time=82,357.670..82,357.670 rows=1 loops=1)

2. 543.216 82,091.020 ↓ 1.4 524,422 1

Nested Loop Left Join (cost=2,096,368.63..161,428,576.50 rows=367,863 width=12) (actual time=24,701.219..82,091.020 rows=524,422 loops=1)

3. 788.877 65,815.144 ↓ 1.4 524,422 1

Nested Loop Left Join (cost=2,096,152.02..81,737,885.11 rows=367,863 width=41) (actual time=24,701.154..65,815.144 rows=524,422 loops=1)

4. 10,120.222 25,170.195 ↓ 1.4 524,422 1

HashAggregate (cost=2,095,935.57..2,099,614.20 rows=367,863 width=33) (actual time=24,701.012..25,170.195 rows=524,422 loops=1)

  • Group Key: x.person_id
5. 15,049.973 15,049.973 ↑ 1.0 22,607,889 1

Seq Scan on event_pg x (cost=0.00..1,982,827.45 rows=22,621,623 width=33) (actual time=0.014..15,049.973 rows=22,607,889 loops=1)

  • Filter: ((timestamp_ >= '2016-01-09 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2016-06-30 23:59:59.999'::timestamp without time zone) AND ((location_host)::text = '2015.testonline.ca'::text))
  • Rows Removed by Filter: 9341052
6. 0.000 39,856.072 ↓ 0.0 0 524,422

Limit (cost=216.45..216.46 rows=1 width=33) (actual time=0.076..0.076 rows=0 loops=524,422)

7. 2,097.688 39,856.072 ↓ 0.0 0 524,422

Sort (cost=216.45..216.46 rows=1 width=33) (actual time=0.076..0.076 rows=0 loops=524,422)

  • Sort Key: a.timestamp_
  • Sort Method: quicksort Memory: 25kB
8. 37,758.384 37,758.384 ↓ 3.0 3 524,422

Index Scan using event_pg_alias on event_pg a (cost=0.56..216.44 rows=1 width=33) (actual time=0.054..0.072 rows=3 loops=524,422)

  • Index Cond: (person_id = x.person_id)
  • Filter: ((timestamp_ >= (min(x.timestamp_))) AND (timestamp_ <= '2016-06-30 23:59:59.999'::timestamp without time zone) AND ((location_host)::text = 'helpcentre.testonline.ca'::text))
  • Rows Removed by Filter: 50
9. 524.422 15,732.660 ↓ 0.0 0 524,422

Limit (cost=216.61..216.61 rows=1 width=8) (actual time=0.030..0.030 rows=0 loops=524,422)

10. 1,048.844 15,208.238 ↓ 0.0 0 524,422

Sort (cost=216.61..216.61 rows=1 width=8) (actual time=0.029..0.029 rows=0 loops=524,422)

  • Sort Key: b.timestamp_
  • Sort Method: quicksort Memory: 25kB
11. 14,159.394 14,159.394 ↓ 0.0 0 524,422

Index Scan using event_pg_alias on event_pg b (cost=0.56..216.60 rows=1 width=8) (actual time=0.027..0.027 rows=0 loops=524,422)

  • Index Cond: (person_id = a.person_id)
  • Filter: ((timestamp_ >= a.timestamp_) AND (timestamp_ <= '2016-06-30 23:59:59.999'::timestamp without time zone) AND ((location_fragment)::text = '/file/thank-you'::text) AND ((location_host)::text = 'helpcentre.testonline.ca': (...)
  • Rows Removed by Filter: 36
Planning time : 0.749 ms
Execution time : 82,366.518 ms