explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v51h : Slow window aggregation due to quicksort

Settings
# exclusive inclusive rows x rows loops node
1. 2,089.432 157,945.405 ↓ 3.5 179,789 1

Subquery Scan on innerstep (cost=3,047,865.12..3,381,828.49 rows=51,379 width=63) (actual time=144,515.394..157,945.405 rows=179,789 loops=1)

  • Filter: (innerstep.rn = 1)
  • Rows Removed by Filter: 10273375
2. 7,817.705 155,855.973 ↓ 1.0 10,453,164 1

WindowAgg (cost=3,047,865.12..3,253,381.04 rows=10,275,796 width=63) (actual time=144,515.377..155,855.973 rows=10,453,164 loops=1)

3. 126,470.285 148,038.268 ↓ 1.0 10,453,164 1

Sort (cost=3,047,865.12..3,073,554.61 rows=10,275,796 width=55) (actual time=144,515.353..148,038.268 rows=10,453,164 loops=1)

  • Sort Key: a.person_id, event_page_2016_8.timestamp_
  • Sort Method: quicksort Memory: 1863192kB
4. 6,417.134 21,567.983 ↓ 1.0 10,453,164 1

Hash Join (cost=112,792.38..1,851,107.54 rows=10,275,796 width=55) (actual time=2,479.712..21,567.983 rows=10,453,164 loops=1)

  • Hash Cond: ((event_page_2016_8.person_alias)::text = (a.alias)::text)
5. 5,817.073 12,692.455 ↓ 1.0 10,453,164 1

Append (cost=0.55..1,571,334.02 rows=10,275,796 width=39) (actual time=0.140..12,692.455 rows=10,453,164 loops=1)

  • -> Index Only Scan using ep_2017_2_host_ts_a_ses on event_page_2017_2 (cost=0.56..445997.01 rows=4247698 width=39) (actual time=0.019..2552.665 rows=44326 (...)
6. 0.016 0.016 ↓ 0.0 0 1

Index Only Scan using ep_2016_8_host_ts_a_ses on event_page_2016_8 (cost=0.55..4.57 rows=1 width=42) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
7. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using ep_2016_1_host_ts_a_ses on event_page_2016_1 (cost=0.41..4.44 rows=1 width=52) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
8. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ep_2016_3_host_ts_a_ses on event_page_2016_3 (cost=0.56..4.58 rows=1 width=41) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
9. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using ep_2016_2_host_ts_a_ses on event_page_2016_2 (cost=0.56..4.58 rows=1 width=49) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
10. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using ep_2016_5_host_ts_a_ses on event_page_2016_5 (cost=0.56..4.58 rows=1 width=41) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
11. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ep_2016_4_host_ts_a_ses on event_page_2016_4 (cost=0.56..4.58 rows=1 width=41) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
12. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using ep_2016_6_host_ts_a_ses on event_page_2016_6 (cost=0.55..4.58 rows=1 width=41) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
13. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on event_page_2015_11 (cost=0.00..1.07 rows=1 width=53) (actual time=0.013..0.013 rows=0 loops=1)

  • Filter: ((timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone) AND ((location_host)::text ~~ '2016.myhost.ca'::text))
  • Rows Removed by Filter: 4
14. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ep_2016_7_host_ts_a_ses on event_page_2016_7 (cost=0.55..4.58 rows=1 width=42) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
15. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using ep_2016_9_host_ts_a_ses on event_page_2016_9 (cost=0.55..4.57 rows=1 width=42) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
16. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using ep_2016_10_host_ts_a_ses on event_page_2016_10 (cost=0.55..4.57 rows=1 width=43) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
17. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using ep_2016_11_host_ts_a_ses on event_page_2016_11 (cost=0.55..4.57 rows=1 width=45) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
18. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ep_2016_12_host_ts_a_ses on event_page_2016_12 (cost=0.55..4.57 rows=1 width=48) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
19. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ep_2017_1_host_ts_a_ses on event_page_2017_1 (cost=0.55..4.58 rows=1 width=45) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
  • Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))
  • Filter: ((location_host)::text ~~ '2016.myhost.ca'::text)
  • Heap Fetches: 0
20. 6,875.267 6,875.267 ↑ 1.0 6,020,487 1

Seq Scan on event_page_2017_3 (cost=0.00..1,125,276.59 rows=6,028,084 width=39) (actual time=0.033..6,875.267 rows=6,020,487 loops=1)

  • Filter: ((timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone) AND ((location_host)::text ~~ '2016.myhost.ca'::text))
  • Rows Removed by Filter: 4221261
21. 1,507.155 2,458.394 ↑ 1.0 3,330,837 1

Hash (cost=71,156.37..71,156.37 rows=3,330,837 width=46) (actual time=2,458.394..2,458.394 rows=3,330,837 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 286925kB
22. 951.239 951.239 ↑ 1.0 3,330,837 1

Seq Scan on alias a (cost=0.00..71,156.37 rows=3,330,837 width=46) (actual time=0.007..951.239 rows=3,330,837 loops=1)

Planning time : 17.973 ms
Execution time : 158,159.783 ms