explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CJO2

Settings
# exclusive inclusive rows x rows loops node
1. 0.326 29,001.209 ↑ 2.2 4,462 1

Limit (cost=2,683,314.51..2,683,339.51 rows=10,000 width=16) (actual time=29,000.463..29,001.209 rows=4,462 loops=1)

2.          

CTE topusers

3. 0.210 25,843.984 ↑ 1.0 100 1

Limit (cost=1,531,901.33..1,531,901.58 rows=100 width=36) (actual time=25,843.755..25,843.984 rows=100 loops=1)

4. 143.650 25,843.774 ↑ 763.5 100 1

Sort (cost=1,531,901.33..1,532,092.19 rows=76,345 width=36) (actual time=25,843.754..25,843.774 rows=100 loops=1)

  • Sort Key: (sum(f_sessions.article_bytes)) DESC
  • Sort Method: top-N heapsort Memory: 29kB
5. 2,550.433 25,700.124 ↓ 8.7 660,980 1

Finalize HashAggregate (cost=1,528,029.17..1,528,983.48 rows=76,345 width=36) (actual time=25,437.391..25,700.124 rows=660,980 loops=1)

  • Group Key: f_sessions.wm_id
  • Filter: (count(*) <= 10000)
  • Rows Removed by Filter: 24
6. 1,204.933 23,149.691 ↓ 9.2 2,799,723 1

Gather (cost=1,493,483.05..1,524,975.37 rows=305,380 width=44) (actual time=21,307.738..23,149.691 rows=2,799,723 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 11,845.154 21,944.758 ↓ 7.3 559,945 5

Partial HashAggregate (cost=1,492,483.05..1,493,437.37 rows=76,345 width=44) (actual time=21,297.950..21,944.758 rows=559,945 loops=5)

  • Group Key: f_sessions.wm_id
8. 1,178.604 10,099.604 ↑ 1.3 13,894,492 5

Append (cost=0.00..1,361,569.02 rows=17,455,204 width=12) (actual time=0.071..10,099.604 rows=13,894,492 loops=5)

9. 0.001 0.001 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=5)

10. 1,895.147 1,895.147 ↑ 1.3 2,808,642 5

Parallel Seq Scan on f_sessions_201805 (cost=0.00..270,089.95 rows=3,510,995 width=12) (actual time=0.069..1,895.147 rows=2,808,642 loops=5)

11. 962.923 962.923 ↑ 1.3 1,452,819 5

Parallel Seq Scan on f_sessions_201806 (cost=0.00..139,618.46 rows=1,816,146 width=12) (actual time=0.034..962.923 rows=1,452,819 loops=5)

12. 935.625 935.625 ↑ 1.3 1,432,364 5

Parallel Seq Scan on f_sessions_201807 (cost=0.00..137,683.73 rows=1,790,474 width=12) (actual time=0.037..935.625 rows=1,432,364 loops=5)

13. 1,044.961 1,044.961 ↑ 1.2 1,488,536 5

Parallel Seq Scan on f_sessions_201808 (cost=0.00..143,057.66 rows=1,860,666 width=12) (actual time=0.038..1,044.961 rows=1,488,536 loops=5)

14. 949.663 949.663 ↑ 1.3 1,490,317 5

Parallel Seq Scan on f_sessions_201809 (cost=0.00..143,272.09 rows=1,862,909 width=12) (actual time=0.054..949.663 rows=1,490,317 loops=5)

15. 1,122.952 1,122.952 ↑ 1.3 1,829,557 5

Parallel Seq Scan on f_sessions_201810 (cost=0.00..184,448.88 rows=2,287,388 width=12) (actual time=0.035..1,122.952 rows=1,829,557 loops=5)

16. 1,012.549 1,012.549 ↑ 1.3 1,640,561 5

Parallel Seq Scan on f_sessions_201811 (cost=0.00..165,874.50 rows=2,050,950 width=12) (actual time=0.035..1,012.549 rows=1,640,561 loops=5)

17. 934.936 934.936 ↑ 1.2 1,650,051 5

Parallel Seq Scan on f_sessions_201812 (cost=0.00..166,810.75 rows=2,062,475 width=12) (actual time=0.051..934.936 rows=1,650,051 loops=5)

18. 62.240 62.240 ↑ 2.1 101,645 5

Parallel Seq Scan on f_sessions_201901 (cost=0.00..10,648.60 rows=211,760 width=12) (actual time=0.050..62.240 rows=101,645 loops=5)

19. 0.001 0.001 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions_201902 (cost=0.00..12.88 rows=288 width=12) (actual time=0.001..0.001 rows=0 loops=5)

20. 0.001 0.001 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions_201903 (cost=0.00..12.88 rows=288 width=12) (actual time=0.001..0.001 rows=0 loops=5)

21. 0.001 0.001 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions_201904 (cost=0.00..12.88 rows=288 width=12) (actual time=0.001..0.001 rows=0 loops=5)

22. 0.000 0.000 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions_201905 (cost=0.00..12.88 rows=288 width=12) (actual time=0.000..0.000 rows=0 loops=5)

23. 0.000 0.000 ↓ 0.0 0 5

Parallel Seq Scan on f_sessions_201906 (cost=0.00..12.88 rows=288 width=12) (actual time=0.000..0.000 rows=0 loops=5)

24.          

CTE limitedsessions

25. 11.872 26,007.650 ↑ 1.3 67,560 1

Nested Loop (cost=2.25..94,841.85 rows=91,006 width=16) (actual time=25,844.162..26,007.650 rows=67,560 loops=1)

26. 0.150 25,844.178 ↑ 1.0 100 1

HashAggregate (cost=2.25..3.25 rows=100 width=4) (actual time=25,844.080..25,844.178 rows=100 loops=1)

  • Group Key: topusers.wm_id
27. 25,844.028 25,844.028 ↑ 1.0 100 1

CTE Scan on topusers (cost=0.00..2.00 rows=100 width=4) (actual time=25,843.780..25,844.028 rows=100 loops=1)

28. 7.700 151.600 ↑ 1.4 676 100

Append (cost=0.00..936.55 rows=956 width=16) (actual time=0.029..1.516 rows=676 loops=100)

29. 0.000 0.000 ↓ 0.0 0 100

Seq Scan on f_sessions f_sessions_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=100)

  • Filter: (topusers.wm_id = wm_id)
30. 23.800 23.800 ↑ 1.8 104 100

Index Scan using f_sessions_201805__wm_id on f_sessions_201805 f_sessions_201805_1 (cost=0.43..179.70 rows=182 width=16) (actual time=0.021..0.238 rows=104 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
31. 17.800 17.800 ↑ 1.2 78 100

Index Scan using f_sessions_201806__wm_id on f_sessions_201806 f_sessions_201806_1 (cost=0.43..95.37 rows=96 width=16) (actual time=0.021..0.178 rows=78 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
32. 18.300 18.300 ↑ 1.3 80 100

Index Scan using f_sessions_201807__wm_id on f_sessions_201807 f_sessions_201807_1 (cost=0.43..100.03 rows=101 width=16) (actual time=0.020..0.183 rows=80 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
33. 18.100 18.100 ↑ 1.2 83 100

Index Scan using f_sessions_201808__wm_id on f_sessions_201808 f_sessions_201808_1 (cost=0.43..102.05 rows=103 width=16) (actual time=0.021..0.181 rows=83 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
34. 20.600 20.600 ↑ 1.3 76 100

Index Scan using f_sessions_201809__wm_id on f_sessions_201809 f_sessions_201809_1 (cost=0.43..101.11 rows=102 width=16) (actual time=0.022..0.206 rows=76 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
35. 16.300 16.300 ↑ 1.4 91 100

Index Scan using f_sessions_201810__wm_id on f_sessions_201810 f_sessions_201810_1 (cost=0.43..125.43 rows=127 width=16) (actual time=0.022..0.163 rows=91 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
36. 13.000 13.000 ↑ 1.4 78 100

Index Scan using f_sessions_201811__wm_id on f_sessions_201811 f_sessions_201811_1 (cost=0.43..110.87 rows=112 width=16) (actual time=0.022..0.130 rows=78 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
37. 13.400 13.400 ↑ 1.4 80 100

Index Scan using f_sessions_201812__wm_id on f_sessions_201812 f_sessions_201812_1 (cost=0.43..112.92 rows=114 width=16) (actual time=0.023..0.134 rows=80 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
38. 2.100 2.100 ↑ 2.0 4 100

Index Scan using f_sessions_201901__wm_id on f_sessions_201901 f_sessions_201901_1 (cost=0.42..7.60 rows=8 width=16) (actual time=0.017..0.021 rows=4 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
39. 0.100 0.100 ↓ 0.0 0 100

Index Scan using f_sessions_201902__wm_id on f_sessions_201902 f_sessions_201902_1 (cost=0.15..0.29 rows=2 width=16) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
40. 0.100 0.100 ↓ 0.0 0 100

Index Scan using f_sessions_201903__wm_id on f_sessions_201903 f_sessions_201903_1 (cost=0.15..0.29 rows=2 width=16) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
41. 0.100 0.100 ↓ 0.0 0 100

Index Scan using f_sessions_201904__wm_id on f_sessions_201904 f_sessions_201904_1 (cost=0.15..0.29 rows=2 width=16) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
42. 0.100 0.100 ↓ 0.0 0 100

Index Scan using f_sessions_201905__wm_id on f_sessions_201905 f_sessions_201905_1 (cost=0.15..0.29 rows=2 width=16) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
43. 0.100 0.100 ↓ 0.0 0 100

Index Scan using f_sessions_201906__wm_id on f_sessions_201906 f_sessions_201906_1 (cost=0.15..0.29 rows=2 width=16) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (wm_id = topusers.wm_id)
44. 2.260 29,000.883 ↑ 9.0 4,462 1

Sort (cost=1,056,571.08..1,056,671.08 rows=40,000 width=16) (actual time=29,000.461..29,000.883 rows=4,462 loops=1)

  • Sort Key: (sum(power(((ls1.article_bytes - ls2.article_bytes))::double precision, '2'::double precision)))
  • Sort Method: quicksort Memory: 402kB
45. 1,111.274 28,998.623 ↑ 9.0 4,462 1

HashAggregate (cost=1,053,313.54..1,053,713.54 rows=40,000 width=16) (actual time=28,997.794..28,998.623 rows=4,462 loops=1)

  • Group Key: ls1.wm_id, ls2.wm_id
46. 1,287.405 27,887.349 ↑ 4.6 2,985,751 1

Merge Join (cost=18,632.27..847,296.50 rows=13,734,469 width=24) (actual time=26,064.532..27,887.349 rows=2,985,751 loops=1)

  • Merge Cond: (ls1.task_date = ls2.task_date)
  • Join Filter: ((ls1.wm_id <> ls2.wm_id) AND (ls1.wm_id < ls2.wm_id))
  • Rows Removed by Join Filter: 3318103
47. 22.370 26,052.310 ↑ 3.8 24,065 1

Sort (cost=9,316.14..9,543.65 rows=91,006 width=16) (actual time=26,045.660..26,052.310 rows=24,065 loops=1)

  • Sort Key: ls1.task_date
  • Sort Method: quicksort Memory: 6991kB
48. 26,029.940 26,029.940 ↑ 1.3 67,560 1

CTE Scan on limitedsessions ls1 (cost=0.00..1,820.12 rows=91,006 width=16) (actual time=25,844.165..26,029.940 rows=67,560 loops=1)

49. 540.332 547.634 ↓ 69.3 6,303,855 1

Sort (cost=9,316.14..9,543.65 rows=91,006 width=16) (actual time=18.849..547.634 rows=6,303,855 loops=1)

  • Sort Key: ls2.task_date
  • Sort Method: quicksort Memory: 6991kB
50. 7.302 7.302 ↑ 1.3 67,560 1

CTE Scan on limitedsessions ls2 (cost=0.00..1,820.12 rows=91,006 width=16) (actual time=0.000..7.302 rows=67,560 loops=1)