explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oSnC

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 67,916.034 ↑ 18.2 11 1

Hash Left Join (cost=2,419,639.36..2,419,705.94 rows=200 width=588) (actual time=66,317.603..67,916.034 rows=11 loops=1)

  • Hash Cond: ((f.multi_series)::text = (f_1.multi_series)::text)
2.          

CTE step_0

3. 832.527 58,011.766 ↓ 123.3 1,594,463 1

Subquery Scan on innerq (cost=2,021,527.00..2,105,574.80 rows=12,930 width=63) (actual time=53,928.839..58,011.766 rows=1,594,463 loops=1)

  • Filter: (innerq.rn = 1)
  • Rows Removed by Filter: 949472
4. 2,445.422 57,179.239 ↑ 1.0 2,543,935 1

WindowAgg (cost=2,021,527.00..2,073,248.72 rows=2,586,086 width=63) (actual time=53,928.837..57,179.239 rows=2,543,935 loops=1)

5. 3,513.679 54,733.817 ↑ 1.0 2,543,935 1

Sort (cost=2,021,527.00..2,027,992.22 rows=2,586,086 width=55) (actual time=53,928.817..54,733.817 rows=2,543,935 loops=1)

  • Sort Key: session_71a5a5bd_001a_4097_9d47_d5c91518de2d.person_id, session_71a5a5bd_001a_4097_9d47_d5c91518de2d.first_seen
  • Sort Method: quicksort Memory: 456045kB
6. 0.000 51,220.138 ↑ 1.0 2,543,935 1

Gather (cost=1,001.13..1,746,078.60 rows=2,586,086 width=55) (actual time=0.738..51,220.138 rows=2,543,935 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 30,069.045 51,646.368 ↑ 1.3 508,787 5

Nested Loop Left Join (cost=1.13..1,486,470.00 rows=646,522 width=55) (actual time=1.204..51,646.368 rows=508,787 loops=5)

8. 21,577.266 21,577.266 ↑ 1.3 508,787 5

Parallel Index Only Scan using session_71a5a5bd_001a_4097_9d47_d5c91518de2d_ts_pid_ses on session_71a5a5bd_001a_4097_9d47_d5c91518de2d (cost=0.56..283,385.03 rows=646,522 width=45) (actual time=1.148..21,577.266 rows=508,787 loops=5)

  • Index Cond: ((first_seen >= '2018-11-01 04:00:00'::timestamp without time zone) AND (first_seen <= '2018-12-01 04:59:59.999'::timestamp without time zone))
  • Heap Fetches: 1329799
9. 0.057 0.057 ↑ 1.0 1 2,543,935

Index Scan using session_71a5a5bd_001a_4097_9d47_d5c91518de2d_id_hash on session_71a5a5bd_001a_4097_9d47_d5c91518de2d s (cost=0.56..1.85 rows=1 width=47) (actual time=0.032..0.057 rows=1 loops=2,543,935)

  • 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
10.          

CTE step_1

11. 20.185 80.143 ↑ 1.0 54,649 1

Append (cost=0.00..44,218.41 rows=55,751 width=46) (actual time=0.072..80.143 rows=54,649 loops=1)

12. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Filter: ((timestamp_ >= '2018-11-01 04: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))
13. 59.952 59.952 ↑ 1.0 54,649 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..44,218.41 rows=55,750 width=46) (actual time=0.065..59.952 rows=54,649 loops=1)

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

CTE join1

15. 1,408.115 63,859.884 ↓ 1.3 1,598,841 1

WindowAgg (cost=191,114.39..215,143.07 rows=1,201,434 width=552) (actual time=62,124.221..63,859.884 rows=1,598,841 loops=1)

16. 1,056.378 62,451.769 ↓ 1.3 1,598,841 1

Sort (cost=191,114.39..194,117.97 rows=1,201,434 width=454) (actual time=62,124.196..62,451.769 rows=1,598,841 loops=1)

  • Sort Key: s1.person_id, s1.step_ts_min
  • Sort Method: quicksort Memory: 273990kB
17. 801.556 61,395.391 ↓ 1.3 1,598,841 1

Merge Left Join (cost=6,651.69..69,791.62 rows=1,201,434 width=454) (actual time=60,262.642..61,395.391 rows=1,598,841 loops=1)

  • Merge Cond: (s0.person_id = s1.person_id)
  • Join Filter: (s0.step_ts_min < s1.step_ts_min)
  • Rows Removed by Join Filter: 25
18. 1,125.374 60,422.940 ↓ 123.3 1,594,463 1

Sort (cost=1,141.62..1,173.94 rows=12,930 width=364) (actual time=60,109.185..60,422.940 rows=1,594,463 loops=1)

  • Sort Key: s0.person_id
  • Sort Method: quicksort Memory: 273374kB
19. 59,297.566 59,297.566 ↓ 123.3 1,594,463 1

CTE Scan on step_0 s0 (cost=0.00..258.60 rows=12,930 width=364) (actual time=53,928.842..59,297.566 rows=1,594,463 loops=1)

20. 53.880 170.895 ↑ 1.0 54,649 1

Sort (cost=5,510.07..5,649.45 rows=55,751 width=90) (actual time=153.444..170.895 rows=54,649 loops=1)

  • Sort Key: s1.person_id
  • Sort Method: quicksort Memory: 5806kB
21. 117.015 117.015 ↑ 1.0 54,649 1

CTE Scan on step_1 s1 (cost=0.00..1,115.02 rows=55,751 width=90) (actual time=0.076..117.015 rows=54,649 loops=1)

22. 1,223.648 67,630.748 ↑ 18.2 11 1

GroupAggregate (cost=27,409.28..27,471.35 rows=200 width=290) (actual time=66,032.360..67,630.748 rows=11 loops=1)

  • Group Key: f.multi_series
23. 1,184.752 66,407.100 ↓ 265.4 1,594,463 1

Sort (cost=27,409.28..27,424.29 rows=6,007 width=306) (actual time=66,032.299..66,407.100 rows=1,594,463 loops=1)

  • Sort Key: f.multi_series
  • Sort Method: quicksort Memory: 173720kB
24. 65,222.348 65,222.348 ↓ 265.4 1,594,463 1

CTE Scan on join1 f (cost=0.00..27,032.26 rows=6,007 width=306) (actual time=62,124.227..65,222.348 rows=1,594,463 loops=1)

  • Filter: (rank1 = 1)
  • Rows Removed by Filter: 4378
25. 0.019 285.221 ↑ 22.2 9 1

Hash (cost=27,291.31..27,291.31 rows=200 width=298) (actual time=285.221..285.221 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 23.530 285.202 ↑ 22.2 9 1

HashAggregate (cost=27,286.81..27,289.31 rows=200 width=298) (actual time=285.197..285.202 rows=9 loops=1)

  • Group Key: f_1.multi_series
27. 54.881 261.672 ↓ 9.0 50,230 1

HashAggregate (cost=27,092.03..27,175.51 rows=5,565 width=298) (actual time=235.288..261.672 rows=50,230 loops=1)

  • Group Key: f_1.multi_series, f_1.p0
28. 206.791 206.791 ↓ 8.4 50,230 1

CTE Scan on join1 f_1 (cost=0.00..27,032.26 rows=5,977 width=306) (actual time=0.004..206.791 rows=50,230 loops=1)

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