explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yR47

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 161,548.651 ↑ 18.2 11 1

Hash Left Join (cost=2,426,693.85..2,426,760.41 rows=200 width=588) (actual time=159,904.887..161,548.651 rows=11 loops=1)

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

CTE step_0

3. 941.428 126,484.996 ↓ 123.6 1,602,716 1

Subquery Scan on innerq (cost=2,028,586.58..2,112,898.60 rows=12,971 width=63) (actual time=122,146.125..126,484.996 rows=1,602,716 loops=1)

  • Filter: (innerq.rn = 1)
  • Rows Removed by Filter: 957441
4. 2,577.368 125,543.568 ↑ 1.0 2,560,157 1

WindowAgg (cost=2,028,586.58..2,080,470.90 rows=2,594,216 width=63) (actual time=122,146.122..125,543.568 rows=2,560,157 loops=1)

5. 3,660.161 122,966.200 ↑ 1.0 2,560,157 1

Sort (cost=2,028,586.58..2,035,072.12 rows=2,594,216 width=55) (actual time=122,146.098..122,966.200 rows=2,560,157 loops=1)

  • Sort Key: session_71a5a5bd_001a_4097_9d47_d5c91518de2d.person_id, session_71a5a5bd_001a_4097_9d47_d5c91518de2d.first_seen
  • Sort Method: quicksort Memory: 458327kB
6. 0.000 119,306.039 ↑ 1.0 2,560,157 1

Gather (cost=1,001.13..1,752,213.50 rows=2,594,216 width=55) (actual time=5.111..119,306.039 rows=2,560,157 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 82,193.274 119,890.026 ↑ 1.3 512,031 5

Nested Loop Left Join (cost=1.13..1,491,791.90 rows=648,554 width=55) (actual time=2.833..119,890.026 rows=512,031 loops=5)

8. 37,696.593 37,696.593 ↑ 1.3 512,031 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..285,023.52 rows=648,554 width=45) (actual time=1.955..37,696.593 rows=512,031 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: 1394038
9. 0.159 0.159 ↑ 1.0 1 2,560,157

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.039..0.159 rows=1 loops=2,560,157)

  • 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. 30.141 24,737.384 ↑ 1.0 54,908 1

Append (cost=0.00..44,102.39 rows=55,547 width=46) (actual time=3.513..24,737.384 rows=54,908 loops=1)

12. 0.008 0.008 ↓ 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.008..0.008 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. 24,663.317 24,663.317 ↑ 1.0 54,800 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..43,994.31 rows=55,428 width=46) (actual time=3.503..24,663.317 rows=54,800 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: 47811
14. 43.918 43.918 ↑ 1.1 108 1

Index Only Scan using ecom_71a5a5bd_001a_4097_9d47_d5c91518de2d_2018_12_st_ts_pid_ses on event_ecommerce_71a5a5bd_001a_4097_9d47_d5c91518de2d_2018_12 o_2 (cost=0.41..108.08 rows=118 width=46) (actual time=0.080..43.918 rows=108 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: 108
15.          

CTE join1

16. 1,436.855 157,360.728 ↓ 1.3 1,607,137 1

WindowAgg (cost=191,000.44..215,017.12 rows=1,200,834 width=552) (actual time=155,576.083..157,360.728 rows=1,607,137 loops=1)

17. 1,136.000 155,923.873 ↓ 1.3 1,607,137 1

Sort (cost=191,000.44..194,002.53 rows=1,200,834 width=454) (actual time=155,576.053..155,923.873 rows=1,607,137 loops=1)

  • Sort Key: s1.person_id, s1.step_ts_min
  • Sort Method: quicksort Memory: 275156kB
18. 869.770 154,787.873 ↓ 1.3 1,607,137 1

Merge Left Join (cost=6,633.97..69,742.59 rows=1,200,834 width=454) (actual time=153,557.628..154,787.873 rows=1,607,137 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
19. 1,183.744 129,017.081 ↓ 123.6 1,602,716 1

Sort (cost=1,145.53..1,177.96 rows=12,971 width=364) (actual time=128,673.849..129,017.081 rows=1,602,716 loops=1)

  • Sort Key: s0.person_id
  • Sort Method: quicksort Memory: 274534kB
20. 127,833.337 127,833.337 ↓ 123.6 1,602,716 1

CTE Scan on step_0 s0 (cost=0.00..259.42 rows=12,971 width=364) (actual time=122,146.129..127,833.337 rows=1,602,716 loops=1)

21. 76.563 24,901.022 ↑ 1.0 54,908 1

Sort (cost=5,488.44..5,627.31 rows=55,547 width=90) (actual time=24,883.763..24,901.022 rows=54,908 loops=1)

  • Sort Key: s1.person_id
  • Sort Method: quicksort Memory: 5826kB
22. 24,824.459 24,824.459 ↑ 1.0 54,908 1

CTE Scan on step_1 s1 (cost=0.00..1,110.94 rows=55,547 width=90) (actual time=3.519..24,824.459 rows=54,908 loops=1)

23. 1,240.353 161,256.750 ↑ 18.2 11 1

GroupAggregate (cost=27,395.57..27,457.61 rows=200 width=290) (actual time=159,613.033..161,256.750 rows=11 loops=1)

  • Group Key: f.multi_series
24. 1,254.811 160,016.397 ↓ 266.9 1,602,716 1

Sort (cost=27,395.57..27,410.58 rows=6,004 width=306) (actual time=159,612.969..160,016.397 rows=1,602,716 loops=1)

  • Sort Key: f.multi_series
  • Sort Method: quicksort Memory: 174365kB
25. 158,761.586 158,761.586 ↓ 266.9 1,602,716 1

CTE Scan on join1 f (cost=0.00..27,018.76 rows=6,004 width=306) (actual time=155,576.090..158,761.586 rows=1,602,716 loops=1)

  • Filter: (rank1 = 1)
  • Rows Removed by Filter: 4421
26. 0.022 291.834 ↑ 22.2 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 24.958 291.812 ↑ 22.2 9 1

HashAggregate (cost=27,273.17..27,275.67 rows=200 width=298) (actual time=291.808..291.812 rows=9 loops=1)

  • Group Key: f_1.multi_series
28. 56.731 266.854 ↓ 9.1 50,446 1

HashAggregate (cost=27,078.50..27,161.93 rows=5,562 width=298) (actual time=239.844..266.854 rows=50,446 loops=1)

  • Group Key: f_1.multi_series, f_1.p0
29. 210.123 210.123 ↓ 8.4 50,446 1

CTE Scan on join1 f_1 (cost=0.00..27,018.76 rows=5,974 width=306) (actual time=0.005..210.123 rows=50,446 loops=1)

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