explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y9AI

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 955.717 ↓ 9.0 9 1

Nested Loop Left Join (cost=6,179.67..6,179.73 rows=1 width=588) (actual time=937.666..955.717 rows=9 loops=1)

  • Join Filter: ((f.multi_series)::text = (f_1.multi_series)::text)
  • Rows Removed by Join Filter: 35
2.          

CTE step_0

3. 13.831 786.519 ↓ 2,958.3 32,541 1

Subquery Scan on innerq (cost=6,104.43..6,175.77 rows=11 width=63) (actual time=728.756..786.519 rows=32,541 loops=1)

  • Filter: (innerq.rn = 1)
  • Rows Removed by Filter: 2378
4. 35.105 772.688 ↓ 15.9 34,919 1

WindowAgg (cost=6,104.43..6,148.33 rows=2,195 width=63) (actual time=728.755..772.688 rows=34,919 loops=1)

5. 43.004 737.583 ↓ 15.9 34,919 1

Sort (cost=6,104.43..6,109.92 rows=2,195 width=55) (actual time=728.739..737.583 rows=34,919 loops=1)

  • Sort Key: session_71a5a5bd_001a_4097_9d47_d5c91518de2d.person_id, session_71a5a5bd_001a_4097_9d47_d5c91518de2d.first_seen
  • Sort Method: quicksort Memory: 6447kB
6. 53.914 694.579 ↓ 15.9 34,919 1

Nested Loop Left Join (cost=1.13..5,982.61 rows=2,195 width=55) (actual time=0.079..694.579 rows=34,919 loops=1)

7. 47.042 47.042 ↓ 15.9 34,919 1

Index Only Scan using session_71a5a5bd_001a_4097_9d47_d5c91518de2d_ts_pid_ses on session_71a5a5bd_001a_4097_9d47_d5c91518de2d (cost=0.56..290.59 rows=2,195 width=45) (actual time=0.046..47.042 rows=34,919 loops=1)

  • Index Cond: ((first_seen >= '2018-11-30 05:00:00'::timestamp without time zone) AND (first_seen <= '2018-12-01 04:59:59.999'::timestamp without time zone))
  • Heap Fetches: 89872
8. 593.623 593.623 ↑ 1.0 1 34,919

Index Scan using session_71a5a5bd_001a_4097_9d47_d5c91518de2d_id_hash on session_71a5a5bd_001a_4097_9d47_d5c91518de2d s (cost=0.56..2.58 rows=1 width=47) (actual time=0.016..0.017 rows=1 loops=34,919)

  • Index Cond: ((session_id)::text = (session_71a5a5bd_001a_4097_9d47_d5c91518de2d.session_id)::text)
  • Filter: (person_id = session_71a5a5bd_001a_4097_9d47_d5c91518de2d.person_id)
  • Rows Removed by Filter: 0
9.          

CTE step_1

10. 0.166 0.796 ↓ 231.5 463 1

Append (cost=0.00..2.58 rows=2 width=68) (actual time=0.059..0.796 rows=463 loops=1)

11. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on event_ecommerce_71a5a5bd_001a_4097_9d47_d5c91518de2d o (cost=0.00..0.00 rows=1 width=90) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((timestamp_ >= '2018-11-30 05:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-12-01 04:59:59.999'::timestamp without time zone) AND ((event_sub_type)::text = 'EC_COMPLETED_ORDER'::text))
12. 0.626 0.626 ↓ 463.0 463 1

Index Only Scan using ecom_71a5a5bd_001a_4097_9d47_d5c91518de2d_2018_11_st_ts_pid_ses on event_ecommerce_71a5a5bd_001a_4097_9d47_d5c91518de2d_2018_11 o_1 (cost=0.56..2.58 rows=1 width=46) (actual time=0.053..0.626 rows=463 loops=1)

  • Index Cond: ((event_sub_type = 'EC_COMPLETED_ORDER'::text) AND (timestamp_ >= '2018-11-30 05:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-12-01 04:59:59.999'::timestamp without time zone))
  • Heap Fetches: 471
13.          

CTE join1

14. 28.535 881.666 ↓ 2,959.9 32,559 1

WindowAgg (cost=0.54..0.76 rows=11 width=552) (actual time=846.495..881.666 rows=32,559 loops=1)

15. 22.200 853.131 ↓ 2,959.9 32,559 1

Sort (cost=0.54..0.57 rows=11 width=454) (actual time=846.488..853.131 rows=32,559 loops=1)

  • Sort Key: s1.person_id, s1.step_ts_min
  • Sort Method: quicksort Memory: 5347kB
16. 19.139 830.931 ↓ 2,959.9 32,559 1

Hash Left Join (cost=0.07..0.35 rows=11 width=454) (actual time=730.064..830.931 rows=32,559 loops=1)

  • Hash Cond: (s0.person_id = s1.person_id)
  • Join Filter: (s0.step_ts_min < s1.step_ts_min)
  • Rows Removed by Join Filter: 11
17. 810.505 810.505 ↓ 2,958.3 32,541 1

CTE Scan on step_0 s0 (cost=0.00..0.22 rows=11 width=364) (actual time=728.759..810.505 rows=32,541 loops=1)

18. 0.180 1.287 ↓ 231.5 463 1

Hash (cost=0.04..0.04 rows=2 width=90) (actual time=1.287..1.287 rows=463 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
19. 1.107 1.107 ↓ 231.5 463 1

CTE Scan on step_1 s1 (cost=0.00..0.04 rows=2 width=90) (actual time=0.061..1.107 rows=463 loops=1)

20. 20.070 951.042 ↓ 9.0 9 1

GroupAggregate (cost=0.26..0.28 rows=1 width=290) (actual time=933.032..951.042 rows=9 loops=1)

  • Group Key: f.multi_series
21. 21.655 930.972 ↓ 32,541.0 32,541 1

Sort (cost=0.26..0.26 rows=1 width=306) (actual time=924.382..930.972 rows=32,541 loops=1)

  • Sort Key: f.multi_series
  • Sort Method: quicksort Memory: 3311kB
22. 909.317 909.317 ↓ 32,541.0 32,541 1

CTE Scan on join1 f (cost=0.00..0.25 rows=1 width=306) (actual time=846.499..909.317 rows=32,541 loops=1)

  • Filter: (rank1 = 1)
  • Rows Removed by Filter: 18
23. 0.220 4.644 ↓ 5.0 5 9

HashAggregate (cost=0.29..0.31 rows=1 width=298) (actual time=0.515..0.516 rows=5 loops=9)

  • Group Key: f_1.multi_series
24. 0.376 4.424 ↓ 392.0 392 1

HashAggregate (cost=0.26..0.27 rows=1 width=298) (actual time=4.257..4.424 rows=392 loops=1)

  • Group Key: f_1.multi_series, f_1.p0
25. 4.048 4.048 ↓ 392.0 392 1

CTE Scan on join1 f_1 (cost=0.00..0.25 rows=1 width=306) (actual time=0.002..4.048 rows=392 loops=1)

  • Filter: ((p1 IS NOT NULL) AND (rank1 = 1))
  • Rows Removed by Filter: 32167