explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fPt5

Settings
# exclusive inclusive rows x rows loops node
1. 600.224 18,345.761 ↑ 9.5 21 1

GroupAggregate (cost=2,589,437.97..2,630,784.98 rows=200 width=56) (actual time=17,508.825..18,345.761 rows=21 loops=1)

  • Group Key: (date_trunc('week'::text, ((s4.event_datetime)::date)::timestamp with time zone))
2.          

CTE s1

3. 1,694.487 6,644.654 ↑ 1.0 2,756,101 1

Sort (cost=755,713.06..762,603.31 rows=2,756,101 width=41) (actual time=6,236.734..6,644.654 rows=2,756,101 loops=1)

  • Sort Key: e.event_datetime
  • Sort Method: external merge Disk: 113576kB
4. 1,674.400 4,950.167 ↑ 1.0 2,756,101 1

WindowAgg (cost=0.43..291,322.21 rows=2,756,101 width=41) (actual time=0.127..4,950.167 rows=2,756,101 loops=1)

5. 3,275.767 3,275.767 ↑ 1.0 2,756,101 1

Index Scan using idx_event_datetime_client_id on event_log e (cost=0.43..236,200.19 rows=2,756,101 width=25) (actual time=0.094..3,275.767 rows=2,756,101 loops=1)

6.          

CTE s2

7. 7,283.614 7,283.614 ↑ 1.0 2,756,101 1

CTE Scan on s1 (cost=0.00..62,012.27 rows=2,756,101 width=69) (actual time=6,236.739..7,283.614 rows=2,756,101 loops=1)

8.          

CTE s3

9. 1,661.805 11,527.114 ↑ 1.0 2,756,101 1

WindowAgg (cost=576,037.86..631,159.88 rows=2,756,101 width=77) (actual time=9,550.949..11,527.114 rows=2,756,101 loops=1)

10. 1,757.788 9,865.309 ↑ 1.0 2,756,101 1

Sort (cost=576,037.86..582,928.12 rows=2,756,101 width=69) (actual time=9,550.938..9,865.309 rows=2,756,101 loops=1)

  • Sort Key: s2.client_id, s2.event_datetime
  • Sort Method: external merge Disk: 130432kB
11. 8,107.521 8,107.521 ↑ 1.0 2,756,101 1

CTE Scan on s2 (cost=0.00..55,122.02 rows=2,756,101 width=69) (actual time=6,236.740..8,107.521 rows=2,756,101 loops=1)

12.          

CTE s4

13. 1,566.077 15,134.705 ↑ 1.0 2,756,101 1

WindowAgg (cost=594,874.86..649,996.88 rows=2,756,101 width=85) (actual time=13,287.816..15,134.705 rows=2,756,101 loops=1)

14. 1,105.296 13,568.628 ↑ 1.0 2,756,101 1

Sort (cost=594,874.86..601,765.12 rows=2,756,101 width=77) (actual time=13,287.805..13,568.628 rows=2,756,101 loops=1)

  • Sort Key: s3.client_id, s3.session_id
  • Sort Method: external merge Disk: 157512kB
15. 12,463.332 12,463.332 ↑ 1.0 2,756,101 1

CTE Scan on s3 (cost=0.00..55,122.02 rows=2,756,101 width=77) (actual time=9,550.952..12,463.332 rows=2,756,101 loops=1)

16. 884.006 17,745.537 ↑ 1.0 2,756,101 1

Sort (cost=483,665.62..490,555.87 rows=2,756,101 width=20) (actual time=17,505.883..17,745.537 rows=2,756,101 loops=1)

  • Sort Key: (date_trunc('week'::text, ((s4.event_datetime)::date)::timestamp with time zone))
  • Sort Method: external merge Disk: 70288kB
17. 16,861.531 16,861.531 ↑ 1.0 2,756,101 1

CTE Scan on s4 (cost=0.00..75,792.78 rows=2,756,101 width=20) (actual time=13,287.824..16,861.531 rows=2,756,101 loops=1)

Planning time : 0.463 ms
Execution time : 18,459.561 ms