explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BSvG

Settings
# exclusive inclusive rows x rows loops node
1. 172,098.030 184,756.259 ↓ 0.0 1 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=184,756.258..184,756.259 rows=1 loops=1)

  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=ec2-3-86-21-29.compute-1.amazonaws.com port=5432 dbname=citus
2. 136.927 12,658.229 ↑ 1.0 1 1

Aggregate (cost=2,589,378.15..2,589,378.16 rows=1 width=16) (actual time=12,658.229..12,658.229 rows=1 loops=1)

3.          

CTE e1

4. 698.264 8,767.244 ↓ 29.0 2,439,474 1

Finalize GroupAggregate (cost=2,514,056.75..2,535,378.15 rows=84,158 width=28) (actual time=7,091.404..8,767.244 rows=2,439,474 loops=1)

  • Group Key: uba_defined_event.userid
5. 0.000 8,068.980 ↓ 31.3 5,275,659 1

Gather Merge (cost=2,514,056.75..2,533,694.99 rows=168,316 width=24) (actual time=7,091.396..8,068.980 rows=5,275,659 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 3,567.933 21,730.977 ↓ 20.9 1,758,553 3

Sort (cost=2,513,056.73..2,513,267.12 rows=84,158 width=24) (actual time=7,072.074..7,243.659 rows=1,758,553 loops=3)

  • Sort Key: uba_defined_event.userid
  • Sort Method: external merge Disk: 58664kB
  • Worker 0: Sort Method: external merge Disk: 58312kB
  • Worker 1: Sort Method: external merge Disk: 58560kB
7. 8,341.854 18,163.044 ↓ 20.9 1,758,553 3

Partial HashAggregate (cost=2,505,330.68..2,506,172.26 rows=84,158 width=24) (actual time=5,648.947..6,054.348 rows=1,758,553 loops=3)

  • Group Key: uba_defined_event.userid
8. 9,821.190 9,821.190 ↑ 1.3 11,141,939 3

Parallel Index Only Scan using uba_defined_event_pkey_106299 on uba_defined_event_106299 uba_defined_event (cost=0.82..2,433,681.18 rows=14,329,900 width=24) (actual time=0.024..3,273.730 rows=11,141,939 loops=3)

  • Index Cond: ((siteid = '626-colehaancom'::text) AND (eventgroup = '76a9931a-ec1c-3330-99d8-718d503e000e'::uuid) AND (visittime >= '1564632000000'::bigint) AND (visittime < '1567655999999'::bigint))
  • Heap Fetches: 45113
9.          

CTE e2

10. 9.876 10,843.669 ↓ 7.9 28,728 1

GroupAggregate (cost=13,366.97..14,586.14 rows=3,629 width=36) (actual time=10,440.757..10,843.669 rows=28,728 loops=1)

  • Group Key: e2_1.userid
11. 153.166 10,833.793 ↓ 10.7 38,681 1

Merge Join (cost=13,366.97..14,522.64 rows=3,629 width=32) (actual time=10,440.721..10,833.793 rows=38,681 loops=1)

  • Merge Cond: (e2_1.userid = e1_1.userid)
  • Join Filter: ((e2_1.visittime > e1_1.step1_time) AND (e2_1.visittime < (e1_1.step1_time + '2592000000'::bigint)))
  • Rows Removed by Join Filter: 4
12. 18.714 28.395 ↓ 1.2 38,699 1

Sort (cost=4,799.35..4,881.00 rows=32,661 width=24) (actual time=22.530..28.395 rows=38,699 loops=1)

  • Sort Key: e2_1.userid
  • Sort Method: quicksort Memory: 4560kB
13. 9.681 9.681 ↓ 1.2 38,699 1

Index Only Scan using uba_defined_event_pkey_106299 on uba_defined_event_106299 e2_1 (cost=0.82..2,350.54 rows=32,661 width=24) (actual time=0.022..9.681 rows=38,699 loops=1)

  • Index Cond: ((siteid = '626-colehaancom'::text) AND (eventgroup = '8a9b32ae-921b-df2e-4ffb-6ee7e18cd5d5'::uuid))
  • Heap Fetches: 85
14. 1,137.388 10,652.232 ↓ 29.1 2,449,394 1

Sort (cost=8,567.63..8,778.02 rows=84,158 width=24) (actual time=10,418.168..10,652.232 rows=2,449,394 loops=1)

  • Sort Key: e1_1.userid
  • Sort Method: external sort Disk: 90712kB
15. 9,514.844 9,514.844 ↓ 29.0 2,439,474 1

CTE Scan on e1 e1_1 (cost=0.00..1,683.16 rows=84,158 width=24) (actual time=7,091.407..9,514.844 rows=2,439,474 loops=1)

16. 289.862 12,521.302 ↓ 1.6 2,439,474 1

Merge Right Join (cost=8,854.78..31,778.63 rows=1,527,047 width=8) (actual time=11,984.322..12,521.302 rows=2,439,474 loops=1)

  • Merge Cond: (e2.userid = e1.userid)
17. 5.868 10,857.424 ↓ 7.9 28,728 1

Sort (cost=287.15..296.22 rows=3,629 width=20) (actual time=10,855.578..10,857.424 rows=28,728 loops=1)

  • Sort Key: e2.userid
  • Sort Method: quicksort Memory: 3013kB
18. 10,851.556 10,851.556 ↓ 7.9 28,728 1

CTE Scan on e2 (cost=0.00..72.58 rows=3,629 width=20) (actual time=10,440.759..10,851.556 rows=28,728 loops=1)

19. 1,040.250 1,374.016 ↓ 29.0 2,439,474 1

Sort (cost=8,567.63..8,778.02 rows=84,158 width=20) (actual time=1,128.738..1,374.016 rows=2,439,474 loops=1)

  • Sort Key: e1.userid
  • Sort Method: external sort Disk: 81160kB
20. 333.766 333.766 ↓ 29.0 2,439,474 1

CTE Scan on e1 (cost=0.00..1,683.16 rows=84,158 width=20) (actual time=0.274..333.766 rows=2,439,474 loops=1)

Planning time : 0.356 ms
Execution time : 184,756.273 ms