explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wIdn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.042 697.414 ↑ 95.0 421 1

Subquery Scan on s1 (cost=239,015.07..244,572.48 rows=40,000 width=40) (actual time=693.092..697.414 rows=421 loops=1)

2.          

CTE filter

3. 1.760 18.280 ↑ 1.0 23,475 1

Append (cost=0..1,506.28 rows=23,581 width=351) (actual time=0.021..18.28 rows=23,475 loops=1)

4. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on session session (cost=0..0 rows=1 width=2,143) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
5. 3.006 3.006 ↑ 1.0 4,225 1

Seq Scan on _hyper_4_5704_chunk _hyper_4_5704_chunk (cost=0..251.32 rows=4,225 width=174) (actual time=0.012..3.006 rows=4,225 loops=1)

  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
6. 3.556 3.556 ↑ 1.0 5,252 1

Seq Scan on _hyper_4_5668_chunk _hyper_4_5668_chunk (cost=0..255.29 rows=5,252 width=676) (actual time=0.011..3.556 rows=5,252 loops=1)

  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
7. 3.336 3.336 ↑ 1.0 5,049 1

Seq Scan on _hyper_4_5692_chunk _hyper_4_5692_chunk (cost=0..267.02 rows=5,049 width=173) (actual time=0.009..3.336 rows=5,049 loops=1)

  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
8. 2.283 2.283 ↓ 1.0 3,041 1

Index Scan using _hyper_4_5656_chunk_session_time_idx on _hyper_4_5656_chunk _hyper_4_5656_chunk (cost=0.28..166.44 rows=3,034 width=675) (actual time=0.026..2.283 rows=3,041 loops=1)

  • Index Cond: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
  • Filter: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
9. 4.332 4.332 ↑ 1.0 5,908 1

Index Scan using _hyper_4_5680_chunk_session_tracking_id_index on _hyper_4_5680_chunk _hyper_4_5680_chunk (cost=0.29..566.22 rows=6,020 width=177) (actual time=0.046..4.332 rows=5,908 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
10.          

CTE bucket_user

11. 15.131 200.952 ↓ 99.7 19,932 1

Aggregate (cost=49,379.27..49,381.77 rows=200 width=524) (actual time=194.977..200.952 rows=19,932 loops=1)

12. 20.059 185.821 ↑ 89.7 22,971 1

Merge Join (cost=8,098.28..39,080.86 rows=2,059,682 width=524) (actual time=160.624..185.821 rows=22,971 loops=1)

13. 70.873 88.614 ↓ 1.3 22,971 1

Sort (cost=5,914.05..5,957.72 rows=17,469 width=28) (actual time=85.632..88.614 rows=22,971 loops=1)

  • Sort Key: session_page.session_id
  • Sort Method: quicksort Memory: 2563kB
14. 1.465 17.741 ↓ 1.3 22,971 1

Append (cost=0..4,683.14 rows=17,469 width=28) (actual time=0.028..17.741 rows=22,971 loops=1)

15. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on session_page session_page (cost=0..0 rows=1 width=516) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
16. 1.738 1.738 ↓ 1.0 2,995 1

Index Scan using _hyper_5_5655_chunk_session_page_time_idx on _hyper_5_5655_chunk _hyper_5_5655_chunk (cost=0.28..301.55 rows=2,978 width=28) (actual time=0.02..1.738 rows=2,995 loops=1)

  • Index Cond: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
  • Filter: (is_entrance AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
17. 2.847 2.847 ↓ 1.1 5,177 1

Seq Scan on _hyper_5_5667_chunk _hyper_5_5667_chunk (cost=0..547.06 rows=4,729 width=28) (actual time=0.006..2.847 rows=5,177 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
18. 3.964 3.964 ↓ 1.9 5,752 1

Index Scan using _hyper_5_5679_chunk_session_page_tracking_id_index on _hyper_5_5679_chunk _hyper_5_5679_chunk (cost=0.41..2,115.13 rows=3,006 width=28) (actual time=0.019..3.964 rows=5,752 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
19. 3.759 3.759 ↓ 1.3 4,913 1

Index Scan using _hyper_5_5691_chunk_session_page_tracking_id_index on _hyper_5_5691_chunk _hyper_5_5691_chunk (cost=0.29..816.41 rows=3,834 width=28) (actual time=0.016..3.759 rows=4,913 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
20. 3.961 3.961 ↓ 1.4 4,134 1

Seq Scan on _hyper_5_5703_chunk _hyper_5_5703_chunk (cost=0..902.99 rows=2,921 width=28) (actual time=0.008..3.961 rows=4,134 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
21. 73.015 77.148 ↑ 1.0 23,481 1

Sort (cost=2,184.23..2,243.18 rows=23,581 width=1,040) (actual time=74.986..77.148 rows=23,481 loops=1)

  • Sort Key: filter.session_id
  • Sort Method: quicksort Memory: 4070kB
22. 4.133 4.133 ↑ 1.0 23,475 1

CTE Scan on filter filter (cost=0..471.62 rows=23,581 width=1,040) (actual time=0.002..4.133 rows=23,475 loops=1)

23.          

CTE new_users

24. 33.505 52.657 ↑ 6.5 31 1

Aggregate (cost=11.64..15.14 rows=200 width=16) (actual time=19.088..52.657 rows=31 loops=1)

25. 6.335 19.152 ↓ 99.7 19,932 1

Sort (cost=11.64..12.14 rows=200 width=524) (actual time=17.881..19.152 rows=19,932 loops=1)

  • Sort Key: bucket_user_1.first_hit
  • Sort Method: quicksort Memory: 2326kB
26. 12.817 12.817 ↓ 99.7 19,932 1

CTE Scan on bucket_user bucket_user_1 (cost=0..4 rows=200 width=524) (actual time=0.001..12.817 rows=19,932 loops=1)

27.          

CTE visitor_log1

28. 24.601 269.172 ↑ 89.7 22,971 1

Merge Join (cost=8,098.28..39,080.86 rows=2,059,682 width=524) (actual time=236.599..269.172 rows=22,971 loops=1)

29. 83.288 117.641 ↓ 1.3 22,971 1

Sort (cost=5,914.05..5,957.72 rows=17,469 width=28) (actual time=113.751..117.641 rows=22,971 loops=1)

  • Sort Key: session_page_1.session_id
  • Sort Method: quicksort Memory: 2563kB
30. 1.858 34.353 ↓ 1.3 22,971 1

Append (cost=0..4,683.14 rows=17,469 width=28) (actual time=0.02..34.353 rows=22,971 loops=1)

31. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on session_page session_page_1 (cost=0..0 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
32. 3.132 3.132 ↓ 1.0 2,995 1

Index Scan using _hyper_5_5655_chunk_session_page_time_idx on _hyper_5_5655_chunk _hyper_5_5655_chunk_1 (cost=0.28..301.55 rows=2,978 width=28) (actual time=0.017..3.132 rows=2,995 loops=1)

  • Index Cond: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
  • Filter: (is_entrance AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
33. 5.326 5.326 ↓ 1.1 5,177 1

Seq Scan on _hyper_5_5667_chunk _hyper_5_5667_chunk_1 (cost=0..547.06 rows=4,729 width=28) (actual time=0.012..5.326 rows=5,177 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
34. 8.415 8.415 ↓ 1.9 5,752 1

Index Scan using _hyper_5_5679_chunk_session_page_tracking_id_index on _hyper_5_5679_chunk _hyper_5_5679_chunk_1 (cost=0.41..2,115.13 rows=3,006 width=28) (actual time=0.052..8.415 rows=5,752 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
35. 7.254 7.254 ↓ 1.3 4,913 1

Index Scan using _hyper_5_5691_chunk_session_page_tracking_id_index on _hyper_5_5691_chunk _hyper_5_5691_chunk_1 (cost=0.29..816.41 rows=3,834 width=28) (actual time=0.041..7.254 rows=4,913 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
36. 8.365 8.365 ↓ 1.4 4,134 1

Seq Scan on _hyper_5_5703_chunk _hyper_5_5703_chunk_1 (cost=0..902.99 rows=2,921 width=28) (actual time=0.008..8.365 rows=4,134 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
37. 88.177 126.930 ↑ 1.0 23,481 1

Sort (cost=2,184.23..2,243.18 rows=23,581 width=1,040) (actual time=122.838..126.93 rows=23,481 loops=1)

  • Sort Key: filter_1.session_id
  • Sort Method: quicksort Memory: 4070kB
38. 38.753 38.753 ↑ 1.0 23,475 1

CTE Scan on filter filter_1 (cost=0..471.62 rows=23,581 width=1,040) (actual time=0.03..38.753 rows=23,475 loops=1)

39.          

CTE visit_tracker

40. 11.424 425.476 ↑ 40.3 7,417 1

Merge Join (cost=55,887.56..62,642.45 rows=298,654 width=532) (actual time=409.569..425.476 rows=7,417 loops=1)

41. 15.333 328.279 ↓ 2.2 7,592 1

Sort (cost=49,973.5..49,982.09 rows=3,433 width=1,048) (actual time=327.131..328.279 rows=7,592 loops=1)

  • Sort Key: filter_2.session_id
  • Sort Method: quicksort Memory: 1260kB
42. 22.317 312.946 ↓ 2.2 7,592 1

Hash Join (cost=648.49..49,771.9 rows=3,433 width=1,048) (actual time=250.677..312.946 rows=7,592 loops=1)

43. 276.626 276.626 ↑ 89.7 22,971 1

CTE Scan on visitor_log1 visitor_log1 (cost=0..41,193.64 rows=2,059,682 width=524) (actual time=236.602..276.626 rows=22,971 loops=1)

44. 7.511 14.003 ↓ 23,475.0 23,475 1

Hash (cost=648.48..648.48 rows=1 width=1,040) (actual time=14.003..14.003 rows=23,475 loops=1)

45. 6.492 6.492 ↓ 23,475.0 23,475 1

CTE Scan on filter filter_2 (cost=0..648.48 rows=1 width=1,040) (actual time=0.052..6.492 rows=23,475 loops=1)

  • Filter: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
46. 68.802 85.773 ↓ 1.6 28,540 1

Sort (cost=5,914.05..5,957.72 rows=17,469 width=28) (actual time=82.428..85.773 rows=28,540 loops=1)

  • Sort Key: session_page_2.session_id
  • Sort Method: quicksort Memory: 2563kB
47. 1.400 16.971 ↓ 1.3 22,971 1

Append (cost=0..4,683.14 rows=17,469 width=28) (actual time=0.039..16.971 rows=22,971 loops=1)

48. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on session_page session_page_2 (cost=0..0 rows=1 width=516) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
49. 1.793 1.793 ↓ 1.0 2,995 1

Index Scan using _hyper_5_5655_chunk_session_page_time_idx on _hyper_5_5655_chunk _hyper_5_5655_chunk_2 (cost=0.28..301.55 rows=2,978 width=28) (actual time=0.032..1.793 rows=2,995 loops=1)

  • Index Cond: (("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
  • Filter: (is_entrance AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
50. 2.656 2.656 ↓ 1.1 5,177 1

Seq Scan on _hyper_5_5667_chunk _hyper_5_5667_chunk_2 (cost=0..547.06 rows=4,729 width=28) (actual time=0.007..2.656 rows=5,177 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
51. 3.851 3.851 ↓ 1.9 5,752 1

Index Scan using _hyper_5_5679_chunk_session_page_tracking_id_index on _hyper_5_5679_chunk _hyper_5_5679_chunk_2 (cost=0.41..2,115.13 rows=3,006 width=28) (actual time=0.019..3.851 rows=5,752 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
52. 3.411 3.411 ↓ 1.3 4,913 1

Index Scan using _hyper_5_5691_chunk_session_page_tracking_id_index on _hyper_5_5691_chunk _hyper_5_5691_chunk_2 (cost=0.29..816.41 rows=3,834 width=28) (actual time=0.016..3.411 rows=4,913 loops=1)

  • Index Cond: ((tracking_id)::text = '5c2ed7472175c126645b4182'::text)
  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone))
53. 3.854 3.854 ↓ 1.4 4,134 1

Seq Scan on _hyper_5_5703_chunk _hyper_5_5703_chunk_2 (cost=0..902.99 rows=2,921 width=28) (actual time=0.005..3.854 rows=4,134 loops=1)

  • Filter: (is_entrance AND ("time" >= '2019-08-04 00:00:00'::timestamp without time zone) AND ("time" <= '2019-09-04 23:59:59'::timestamp without time zone) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
54. 3.848 697.372 ↑ 95.0 421 1

Aggregate (cost=86,388.57..91,545.98 rows=40,000 width=48) (actual time=693.091..697.372 rows=421 loops=1)

55. 4.116 693.524 ↑ 40.1 7,417 1

Sort (cost=86,388.57..87,131.47 rows=297,161 width=540) (actual time=693.065..693.524 rows=7,417 loops=1)

  • Sort Key: new_users.first_hit, new_users.new_users, visit_tracker.retention_index
  • Sort Method: quicksort Memory: 1236kB
56. 2.117 689.408 ↑ 40.1 7,417 1

Hash Join (cost=20.5..17,137.12 rows=297,161 width=540) (actual time=668.819..689.408 rows=7,417 loops=1)

57. 428.058 428.058 ↑ 40.1 7,417 1

CTE Scan on visit_tracker visit_tracker (cost=0..5,973.08 rows=297,161 width=524) (actual time=409.572..428.058 rows=7,417 loops=1)

  • Filter: (retention_index IS NOT NULL)
58. 4.973 259.233 ↓ 99.7 19,932 1

Hash (cost=18..18 rows=200 width=532) (actual time=259.233..259.233 rows=19,932 loops=1)

59. 4.799 254.260 ↓ 99.7 19,932 1

Hash Join (cost=6.5..18 rows=200 width=532) (actual time=247.679..254.26 rows=19,932 loops=1)

60. 196.778 196.778 ↓ 99.7 19,932 1

CTE Scan on bucket_user bucket_user (cost=0..4 rows=200 width=524) (actual time=194.981..196.778 rows=19,932 loops=1)

61. 0.012 52.683 ↑ 6.5 31 1

Hash (cost=4..4 rows=200 width=16) (actual time=52.683..52.683 rows=31 loops=1)

62. 52.671 52.671 ↑ 6.5 31 1

CTE Scan on new_users new_users (cost=0..4 rows=200 width=16) (actual time=19.089..52.671 rows=31 loops=1)