explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eNkk

Settings
# exclusive inclusive rows x rows loops node
1. 37.826 93,074.872 ↓ 14.9 431 1

Aggregate (cost=12.59..13.6 rows=29 width=40) (actual time=93,036.906..93,074.872 rows=431 loops=1)

2.          

CTE filter

3. 1.739 16.585 ↑ 1.0 23,475 1

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

4. 0.031 0.031 ↓ 0.0 0 1

Seq Scan on session session (cost=0..0 rows=1 width=2,143) (actual time=0.031..0.031 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. 2.608 2.608 ↑ 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.011..2.608 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.262 3.262 ↑ 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.008..3.262 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. 2.927 2.927 ↑ 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..2.927 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.097 2.097 ↓ 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.028..2.097 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. 3.921 3.921 ↑ 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.036..3.921 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. 18.501 273.532 ↓ 99.7 19,932 1

Aggregate (cost=54,528.47..54,530.97 rows=200 width=532) (actual time=265.522..273.532 rows=19,932 loops=1)

12. 25.213 255.031 ↑ 89.7 22,971 1

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

13. 78.772 108.586 ↓ 1.3 22,971 1

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

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

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

15. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on session_page session_page (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))
16. 2.338 2.338 ↓ 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.018..2.338 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. 4.197 4.197 ↓ 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..4.197 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. 7.571 7.571 ↓ 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.043..7.571 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. 6.187 6.187 ↓ 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.038..6.187 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. 7.796 7.796 ↓ 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..7.796 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. 84.890 121.232 ↑ 1.0 23,481 1

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

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

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

23.          

CTE new_users

24. 35.188 46.532 ↑ 6.5 31 1

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

25. 7.887 11.344 ↓ 99.7 19,932 1

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

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

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

27.          

CTE visit_tracker

28. 31.750 92,649.903 ↓ 792.1 22,971 1

Nested Loop (cost=0..673.3 rows=29 width=524) (actual time=11.563..92,649.903 rows=22,971 loops=1)

29. 49,943.296 91,604.129 ↓ 23,046.0 23,046 1

Nested Loop (cost=0..656.98 rows=1 width=1,048) (actual time=11.39..91,604.129 rows=23,046 loops=1)

30. 16.183 16.183 ↓ 23,475.0 23,475 1

CTE Scan on filter filter_1 (cost=0..648.48 rows=1 width=1,040) (actual time=0.002..16.183 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))
31. 41,644.650 41,644.650 ↓ 99.7 19,932 23,475

CTE Scan on bucket_user bucket_user_2 (cost=0..4 rows=200 width=524) (actual time=0..1.774 rows=19,932 loops=23,475)

32. 115.230 1,014.024 ↑ 6.0 1 23,046

Append (cost=0..16.05 rows=6 width=28) (actual time=0.029..0.044 rows=1 loops=23,046)

33. 0.000 0.000 ↓ 0.0 0 23,046

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

  • 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) AND ((filter_1.session_id)::text = (session_id)::text))
34. 207.414 207.414 ↓ 0.0 0 23,046

Index Scan using _hyper_5_5655_chunk_session_page_session_id_index on _hyper_5_5655_chunk _hyper_5_5655_chunk_1 (cost=0.28..2.51 rows=1 width=28) (actual time=0.009..0.009 rows=0 loops=23,046)

  • Index Cond: ((session_id)::text = (filter_1.session_id)::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) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
35. 161.322 161.322 ↓ 0.0 0 23,046

Index Scan using _hyper_5_5667_chunk_session_page_session_id_index on _hyper_5_5667_chunk _hyper_5_5667_chunk_1 (cost=0.28..2.51 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=23,046)

  • Index Cond: ((session_id)::text = (filter_1.session_id)::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) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
36. 207.414 207.414 ↓ 0.0 0 23,046

Index Scan using _hyper_5_5679_chunk_session_page_session_id_index on _hyper_5_5679_chunk _hyper_5_5679_chunk_1 (cost=0.41..3.76 rows=1 width=28) (actual time=0.009..0.009 rows=0 loops=23,046)

  • Index Cond: ((session_id)::text = (filter_1.session_id)::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) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
37. 161.322 161.322 ↓ 0.0 0 23,046

Index Scan using _hyper_5_5691_chunk_session_page_session_id_index on _hyper_5_5691_chunk _hyper_5_5691_chunk_1 (cost=0.29..3.63 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=23,046)

  • Index Cond: ((session_id)::text = (filter_1.session_id)::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) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
38. 161.322 161.322 ↓ 0.0 0 23,046

Index Scan using _hyper_5_5703_chunk_session_page_session_id_index on _hyper_5_5703_chunk _hyper_5_5703_chunk_1 (cost=0.29..3.63 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=23,046)

  • Index Cond: ((session_id)::text = (filter_1.session_id)::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) AND ((tracking_id)::text = '5c2ed7472175c126645b4182'::text))
39. 8.654 93,037.046 ↓ 792.1 22,971 1

Sort (cost=12.59..12.66 rows=29 width=540) (actual time=93,035.743..93,037.046 rows=22,971 loops=1)

  • Sort Key: new_users.first_hit, visit_tracker.retention_index
  • Sort Method: quicksort Memory: 3998kB
40. 5.901 93,028.392 ↓ 792.1 22,971 1

Hash Join (cost=6.34..11.88 rows=29 width=540) (actual time=92,986.482..93,028.392 rows=22,971 loops=1)

41. 46.600 46.600 ↑ 6.5 31 1

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

42. 5.519 92,975.891 ↓ 792.1 22,971 1

Hash (cost=5.98..5.98 rows=29 width=532) (actual time=92,975.891..92,975.891 rows=22,971 loops=1)

43. 9.392 92,970.372 ↓ 792.1 22,971 1

Hash Join (cost=0.94..5.98 rows=29 width=532) (actual time=92,958.101..92,970.372 rows=22,971 loops=1)

44. 268.425 268.425 ↓ 99.7 19,932 1

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

45. 21.043 92,692.555 ↓ 792.1 22,971 1

Hash (cost=0.58..0.58 rows=29 width=524) (actual time=92,692.555..92,692.555 rows=22,971 loops=1)

46. 92,671.512 92,671.512 ↓ 792.1 22,971 1

CTE Scan on visit_tracker visit_tracker (cost=0..0.58 rows=29 width=524) (actual time=11.567..92,671.512 rows=22,971 loops=1)

  • Filter: (retention_index IS NOT NULL)