explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2tOi : a few days

Settings
# exclusive inclusive rows x rows loops node
1. 2.531 695.675 ↑ 1.0 1 1

Aggregate (cost=1,011,041.94..1,011,041.95 rows=1 width=12) (actual time=695.675..695.675 rows=1 loops=1)

2. 2.767 693.144 ↓ 2.8 5,738 1

Nested Loop Left Join (cost=136,969.62..1,011,026.80 rows=2,019 width=12) (actual time=144.924..693.144 rows=5,738 loops=1)

3. 2.790 581.355 ↓ 2.8 5,738 1

Nested Loop Left Join (cost=136,753.01..573,647.92 rows=2,019 width=41) (actual time=144.917..581.355 rows=5,738 loops=1)

4. 60.279 148.215 ↓ 2.8 5,738 1

HashAggregate (cost=136,536.56..136,556.75 rows=2,019 width=33) (actual time=144.774..148.215 rows=5,738 loops=1)

  • Group Key: x.person_id
5. 87.936 87.936 ↓ 1.0 127,319 1

Index Scan using event_pg_ts on event_pg x (cost=0.56..135,915.96 rows=124,121 width=33) (actual time=0.025..87.936 rows=127,319 loops=1)

  • Index Cond: ((timestamp_ >= '2016-06-23 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2016-06-30 23:59:59.999'::timestamp without time zone))
  • Filter: ((location_host)::text = '2015.testonline.ca'::text)
  • Rows Removed by Filter: 48,039
6. 5.738 430.350 ↓ 0.0 0 5,738

Limit (cost=216.45..216.46 rows=1 width=33) (actual time=0.075..0.075 rows=0 loops=5,738)

7. 11.476 424.612 ↓ 0.0 0 5,738

Sort (cost=216.45..216.46 rows=1 width=33) (actual time=0.074..0.074 rows=0 loops=5,738)

  • Sort Key: a.timestamp_
  • Sort Method: quicksort Memory: 25kB
8. 413.136 413.136 ↑ 1.0 1 5,738

Index Scan using event_pg_alias on event_pg a (cost=0.56..216.44 rows=1 width=33) (actual time=0.065..0.072 rows=1 loops=5,738)

  • 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: 98
9. 5.738 109.022 ↓ 0.0 0 5,738

Limit (cost=216.61..216.61 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=5,738)

10. 11.476 103.284 ↓ 0.0 0 5,738

Sort (cost=216.61..216.61 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=5,738)

  • Sort Key: b.timestamp_
  • Sort Method: quicksort Memory: 25kB
11. 91.808 91.808 ↓ 0.0 0 5,738

Index Scan using event_pg_alias on event_pg b (cost=0.56..216.60 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=5,738)

  • 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.920 ms
Execution time : 695.859 ms