explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dO5H

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 20,681.813 ↑ 18.2 11 1

Hash Left Join (cost=2,077,939.39..2,078,005.95 rows=200 width=588) (actual time=19,036.130..20,681.813 rows=11 loops=1)

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

CTE step_0

3. 945.083 10,281.024 ↓ 123.6 1,602,716 1

Subquery Scan on innerq (cost=1,679,832.12..1,764,144.14 rows=12,971 width=63) (actual time=5,982.021..10,281.024 rows=1,602,716 loops=1)

  • Filter: (innerq.rn = 1)
  • Rows Removed by Filter: 957441
4. 2,563.601 9,335.941 ↑ 1.0 2,560,157 1

WindowAgg (cost=1,679,832.12..1,731,716.44 rows=2,594,216 width=63) (actual time=5,982.019..9,335.941 rows=2,560,157 loops=1)

5. 3,216.634 6,772.340 ↑ 1.0 2,560,157 1

Sort (cost=1,679,832.12..1,686,317.66 rows=2,594,216 width=55) (actual time=5,981.992..6,772.340 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. 3,555.706 3,555.706 ↑ 1.0 2,560,157 1

Index Scan using session_71a5a5bd_001a_4097_9d47_d5c91518de2d_ts_pid_ses on session_71a5a5bd_001a_4097_9d47_d5c91518de2d (cost=0.56..1,403,459.04 rows=2,594,216 width=55) (actual time=0.014..3,555.706 rows=2,560,157 loops=1)

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

CTE step_1

8. 23.172 80.254 ↑ 1.0 54,908 1

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

9. 0.009 0.009 ↓ 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.009..0.009 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))
10. 56.938 56.938 ↑ 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=0.047..56.938 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: 47113
11. 0.135 0.135 ↑ 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.034..0.135 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
12.          

CTE join1

13. 1,454.749 16,444.042 ↓ 1.3 1,607,137 1

WindowAgg (cost=191,000.44..215,017.12 rows=1,200,834 width=552) (actual time=14,639.274..16,444.042 rows=1,607,137 loops=1)

14. 1,136.874 14,989.293 ↓ 1.3 1,607,137 1

Sort (cost=191,000.44..194,002.53 rows=1,200,834 width=454) (actual time=14,639.241..14,989.293 rows=1,607,137 loops=1)

  • Sort Key: s1.person_id, s1.step_ts_min
  • Sort Method: quicksort Memory: 275156kB
15. 878.671 13,852.419 ↓ 1.3 1,607,137 1

Merge Left Join (cost=6,633.97..69,742.59 rows=1,200,834 width=454) (actual time=12,620.904..13,852.419 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
16. 1,176.782 12,797.871 ↓ 123.6 1,602,716 1

Sort (cost=1,145.53..1,177.96 rows=12,971 width=364) (actual time=12,462.249..12,797.871 rows=1,602,716 loops=1)

  • Sort Key: s0.person_id
  • Sort Method: quicksort Memory: 274534kB
17. 11,621.089 11,621.089 ↓ 123.6 1,602,716 1

CTE Scan on step_0 s0 (cost=0.00..259.42 rows=12,971 width=364) (actual time=5,982.024..11,621.089 rows=1,602,716 loops=1)

18. 54.748 175.877 ↑ 1.0 54,908 1

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

  • Sort Key: s1.person_id
  • Sort Method: quicksort Memory: 5826kB
19. 121.129 121.129 ↑ 1.0 54,908 1

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

20. 1,235.344 20,391.642 ↑ 18.2 11 1

GroupAggregate (cost=27,395.57..27,457.61 rows=200 width=290) (actual time=18,746.005..20,391.642 rows=11 loops=1)

  • Group Key: f.multi_series
21. 1,272.960 19,156.298 ↓ 266.9 1,602,716 1

Sort (cost=27,395.57..27,410.58 rows=6,004 width=306) (actual time=18,745.941..19,156.298 rows=1,602,716 loops=1)

  • Sort Key: f.multi_series
  • Sort Method: quicksort Memory: 174365kB
22. 17,883.338 17,883.338 ↓ 266.9 1,602,716 1

CTE Scan on join1 f (cost=0.00..27,018.76 rows=6,004 width=306) (actual time=14,639.279..17,883.338 rows=1,602,716 loops=1)

  • Filter: (rank1 = 1)
  • Rows Removed by Filter: 4421
23. 0.015 290.106 ↑ 22.2 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 24.574 290.091 ↑ 22.2 9 1

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

  • Group Key: f_1.multi_series
25. 55.411 265.517 ↓ 9.1 50,446 1

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

  • Group Key: f_1.multi_series, f_1.p0
26. 210.106 210.106 ↓ 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.106 rows=50,446 loops=1)

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