explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3BuQ

Settings
# exclusive inclusive rows x rows loops node
1. 265,424.366 265,424.366 ↓ 2.3 11,194,290 1

CTE Scan on main (cost=6,653,304.79..6,823,582.59 rows=4,865,080 width=339) (actual time=174,320.675..265,424.366 rows=11,194,290 loops=1)

2.          

CTE first_session

3. 2,954.022 61,518.098 ↓ 2.0 1,717,001 1

Unique (cost=1,539,474.89..1,602,422.85 rows=839,306 width=96) (actual time=55,179.416..61,518.098 rows=1,717,001 loops=1)

4. 53,293.192 58,564.076 ↑ 1.0 8,390,608 1

Sort (cost=1,539,474.89..1,560,457.54 rows=8,393,062 width=96) (actual time=55,179.413..58,564.076 rows=8,390,608 loops=1)

  • Sort Key: session.device_id, session.app_version, session."timestamp
  • Sort Method: external merge Disk: 908,096kB
5. 5,270.884 5,270.884 ↑ 1.0 8,390,608 1

Seq Scan on session (cost=0.00..574,240.62 rows=8,393,062 width=96) (actual time=0.018..5,270.884 rows=8,390,608 loops=1)

6.          

CTE main

7. 4,519.954 235,245.860 ↓ 2.3 11,194,290 1

Append (cost=3,397,992.14..5,050,881.94 rows=4,865,080 width=211) (actual time=174,320.649..235,245.860 rows=11,194,290 loops=1)

8. 12,420.923 197,283.445 ↓ 3.0 9,444,263 1

Merge Left Join (cost=3,397,992.14..3,485,818.46 rows=3,114,607 width=211) (actual time=174,320.648..197,283.445 rows=9,444,263 loops=1)

  • Merge Cond: ((s.app_version = first_session.first_app_version_id) AND (s.device_id = first_session.device_id))
9. 58,253.146 109,476.252 ↓ 3.0 9,444,263 1

Sort (cost=3,298,623.19..3,306,409.71 rows=3,114,607 width=162) (actual time=101,189.520..109,476.252 rows=9,444,263 loops=1)

  • Sort Key: s.app_version, s.device_id
  • Sort Method: external merge Disk: 1,603,696kB
10. 337.915 51,223.106 ↓ 3.0 9,444,263 1

Gather (cost=627,952.32..2,962,703.19 rows=3,114,607 width=162) (actual time=8,491.929..51,223.106 rows=9,444,263 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 4,339.610 50,885.191 ↓ 2.4 3,148,088 3 / 3

Parallel Hash Join (cost=626,952.32..2,650,242.49 rows=1,297,753 width=162) (actual time=8,486.456..50,885.191 rows=3,148,088 loops=3)

  • Hash Cond: (m.session_id = s.session_id)
12. 38,085.392 38,085.392 ↑ 1.2 3,175,159 3 / 3

Parallel Seq Scan on metric_value m (cost=0.00..2,003,283.16 rows=3,893,259 width=83) (actual time=19.816..38,085.392 rows=3,175,159 loops=3)

  • Filter: (metric_name = ANY ('{launchTime,PlaceOrderPageEnterTime,restoreWebWorker,restoreWebWorkerWithReConnect,restoreWebWorkerWithReLogin}'::text[]))
  • Rows Removed by Filter: 19,935,612
13. 2,616.913 8,460.189 ↓ 2.4 2,745,424 3 / 3

Parallel Hash (cost=612,381.03..612,381.03 rows=1,165,703 width=135) (actual time=8,460.189..8,460.189 rows=2,745,424 loops=3)

  • Buckets: 8,388,608 (originally 4194304) Batches: 1 (originally 1) Memory Usage: 1,490,752kB
14. 2,472.241 5,843.276 ↓ 2.4 2,745,424 3 / 3

Parallel Hash Join (cost=40,326.09..612,381.03 rows=1,165,703 width=135) (actual time=737.644..5,843.276 rows=2,745,424 loops=3)

  • Hash Cond: (s.device_id = etl_tbl_all_capital_devices.device_id)
15. 2,639.701 2,639.701 ↓ 2.4 2,745,489 3 / 3

Parallel Seq Scan on session s (cost=0.00..568,994.96 rows=1,165,703 width=105) (actual time=3.323..2,639.701 rows=2,745,489 loops=3)

  • Filter: (date(to_timestamp((("timestamp" / 1000))::double precision)) > '2017-10-01'::date)
  • Rows Removed by Filter: 51,381
16. 304.590 731.334 ↑ 1.3 497,018 3 / 3

Parallel Hash (cost=32,559.93..32,559.93 rows=621,293 width=52) (actual time=731.333..731.334 rows=497,018 loops=3)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 146,240kB
17. 426.744 426.744 ↑ 1.3 497,018 3 / 3

Parallel Seq Scan on etl_tbl_all_capital_devices (cost=0.00..32,559.93 rows=621,293 width=52) (actual time=0.685..426.744 rows=497,018 loops=3)

18. 12,756.777 75,386.270 ↓ 11.3 9,515,225 1

Sort (cost=99,368.95..101,467.22 rows=839,306 width=96) (actual time=73,125.716..75,386.270 rows=9,515,225 loops=1)

  • Sort Key: first_session.first_app_version_id, first_session.device_id
  • Sort Method: quicksort Memory: 340,308kB
19. 62,629.493 62,629.493 ↓ 2.0 1,717,001 1

CTE Scan on first_session (cost=0.00..16,786.12 rows=839,306 width=96) (actual time=55,179.424..62,629.493 rows=1,717,001 loops=1)

20. 1,635.789 33,442.461 ↑ 1.0 1,750,027 1

Hash Left Join (cost=1,187,807.25..1,492,087.27 rows=1,750,473 width=211) (actual time=14,974.418..33,442.461 rows=1,750,027 loops=1)

  • Hash Cond: (l.user_id = d.user_id)
21. 4,007.406 30,065.108 ↑ 1.0 1,750,027 1

Merge Left Join (cost=1,083,541.40..1,333,294.77 rows=1,750,473 width=171) (actual time=13,228.107..30,065.108 rows=1,750,027 loops=1)

  • Merge Cond: (l.device_id = c.device_id)
22. 2,787.146 17,385.546 ↑ 1.0 1,750,027 1

Merge Left Join (cost=1,083,540.84..1,107,940.07 rows=1,750,473 width=195) (actual time=13,202.221..17,385.546 rows=1,750,027 loops=1)

  • Merge Cond: ((l.device_id = first_session_1.device_id) AND (s_1.app_version = first_session_1.first_app_version_id))
23. 6,408.768 12,159.533 ↑ 1.0 1,750,027 1

Sort (cost=984,171.89..988,548.07 rows=1,750,473 width=163) (actual time=11,535.617..12,159.533 rows=1,750,027 loops=1)

  • Sort Key: l.device_id, s_1.app_version
  • Sort Method: quicksort Memory: 500,220kB
24. 336.143 5,750.765 ↑ 1.0 1,750,027 1

Gather (cost=569,994.95..802,653.85 rows=1,750,473 width=163) (actual time=4,150.936..5,750.765 rows=1,750,027 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 670.230 5,414.622 ↑ 1.3 583,342 3 / 3

Parallel Hash Left Join (cost=568,994.95..626,606.55 rows=729,364 width=163) (actual time=4,137.422..5,414.622 rows=583,342 loops=3)

  • Hash Cond: (l.session_id = s_1.session_id)
26. 631.839 631.839 ↑ 1.3 583,338 3 / 3

Parallel Seq Scan on command_latency l (cost=0.00..50,014.05 rows=729,364 width=133) (actual time=0.461..631.839 rows=583,338 loops=3)

  • Filter: (command = 'PLACE_MARKET_ORDER'::text)
  • Rows Removed by Filter: 0
27. 2,255.662 4,112.553 ↑ 1.3 2,796,869 3 / 3

Parallel Hash (cost=525,281.09..525,281.09 rows=3,497,109 width=93) (actual time=4,112.552..4,112.553 rows=2,796,869 loops=3)

  • Buckets: 16,777,216 Batches: 1 Memory Usage: 1,206,560kB
28. 1,856.891 1,856.891 ↑ 1.3 2,796,869 3 / 3

Parallel Seq Scan on session s_1 (cost=0.00..525,281.09 rows=3,497,109 width=93) (actual time=0.022..1,856.891 rows=2,796,869 loops=3)

29. 1,923.144 2,438.867 ↓ 3.9 3,266,561 1

Sort (cost=99,368.95..101,467.22 rows=839,306 width=96) (actual time=1,666.577..2,438.867 rows=3,266,561 loops=1)

  • Sort Key: first_session_1.device_id, first_session_1.first_app_version_id
  • Sort Method: quicksort Memory: 340,308kB
30. 515.723 515.723 ↓ 2.0 1,717,001 1

CTE Scan on first_session first_session_1 (cost=0.00..16,786.12 rows=839,306 width=96) (actual time=0.003..515.723 rows=1,717,001 loops=1)

31. 8,672.156 8,672.156 ↑ 1.5 7,216,917 1

Index Scan using ix_ip_country_session_1 on etl_tbl_first_ip_country_session c (cost=0.56..368,778.31 rows=10,738,292 width=24) (actual time=8.850..8,672.156 rows=7,216,917 loops=1)

32. 170.977 1,741.564 ↑ 1.0 524,021 1

Hash (cost=97,707.44..97,707.44 rows=524,673 width=11) (actual time=1,741.563..1,741.564 rows=524,021 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 31,147kB
33. 225.650 1,570.587 ↑ 1.0 524,021 1

Subquery Scan on d (cost=92,363.22..97,707.44 rows=524,673 width=11) (actual time=801.818..1,570.587 rows=524,021 loops=1)

34. 544.339 1,344.937 ↑ 1.0 524,021 1

Hash Right Join (cost=92,363.22..92,460.71 rows=524,673 width=3,512) (actual time=801.817..1,344.937 rows=524,021 loops=1)

  • Hash Cond: (aff_goals.user_id = u.user_id)
35.          

CTE aff_goals

36. 3.228 12.047 ↓ 1.1 4,556 1

Sort (cost=1,004.01..1,014.78 rows=4,309 width=28) (actual time=11.000..12.047 rows=4,556 loops=1)

  • Sort Key: g.user_id
  • Sort Method: quicksort Memory: 415kB
37. 5.463 8.819 ↓ 1.1 4,556 1

HashAggregate (cost=700.81..743.90 rows=4,309 width=28) (actual time=7.176..8.819 rows=4,556 loops=1)

  • Group Key: g.user_id
38. 3.356 3.356 ↑ 1.0 7,157 1

Seq Scan on tbl_affiliate_goals_for_export g (cost=0.00..503.99 rows=7,157 width=28) (actual time=0.013..3.356 rows=7,157 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,362
39. 14.397 14.397 ↓ 1.1 4,556 1

CTE Scan on aff_goals (cost=0.00..86.18 rows=4,309 width=8) (actual time=11.005..14.397 rows=4,556 loops=1)

40. 162.857 786.201 ↑ 1.0 524,021 1

Hash (cost=84,790.02..84,790.02 rows=524,673 width=11) (actual time=786.201..786.201 rows=524,021 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 29,594kB
41. 623.344 623.344 ↑ 1.0 524,021 1

Seq Scan on etl_dim_ums_users u (cost=0.00..84,790.02 rows=524,673 width=11) (actual time=0.013..623.344 rows=524,021 loops=1)

  • Filter: (user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Rows Removed by Filter: 303,993
Planning time : 11.683 ms
Execution time : 269,025.518 ms