explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S81b

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 179.029 ↑ 6,067.8 8 1

Sort (cost=29,814.74..29,936.09 rows=48,542 width=45) (actual time=179.029..179.029 rows=8 loops=1)

  • Sort Key: usr.id, usr.group_id, usr.name, (first_value(usrs."time") OVER (?))
  • Sort Method: quicksort Memory: 25kB
2. 0.053 179.020 ↑ 6,067.8 8 1

WindowAgg (cost=25,065.64..26,036.48 rows=48,542 width=45) (actual time=179.011..179.020 rows=8 loops=1)

3. 0.032 178.967 ↑ 6,067.8 8 1

Sort (cost=25,065.64..25,187.00 rows=48,542 width=37) (actual time=178.966..178.967 rows=8 loops=1)

  • Sort Key: usr.id, usrs."time" DESC
  • Sort Method: quicksort Memory: 25kB
4. 1.473 178.935 ↑ 6,067.8 8 1

Hash Join (cost=7,448.32..21,287.39 rows=48,542 width=37) (actual time=160.592..178.935 rows=8 loops=1)

  • Hash Cond: (usrs.user_id = usr.id)
  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 1285
5. 21.406 166.780 ↑ 71.7 1,354 1

Hash Join (cost=6,355.37..13,942.34 rows=97,084 width=16) (actual time=107.996..166.780 rows=1,354 loops=1)

  • Hash Cond: (usrs."time" = "ANY_subquery"."time")
6. 37.415 37.415 ↑ 1.0 191,830 1

Seq Scan on user_sessions usrs (cost=0.00..5,997.23 rows=194,167 width=16) (actual time=0.009..37.415 rows=191,830 loops=1)

  • Filter: ("time" < '2018-01-16 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 46080
7. 0.363 107.959 ↓ 12.5 2,500 1

Hash (cost=6,352.87..6,352.87 rows=200 width=8) (actual time=107.959..107.959 rows=2,500 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 130kB
8. 0.953 107.596 ↓ 12.5 2,500 1

HashAggregate (cost=6,350.87..6,352.87 rows=200 width=8) (actual time=107.281..107.596 rows=2,500 loops=1)

  • Group Key: "ANY_subquery"."time
9. 0.251 106.643 ↓ 1.5 2,504 1

Subquery Scan on ANY_subquery (cost=6,312.86..6,346.64 rows=1,689 width=8) (actual time=106.024..106.643 rows=2,504 loops=1)

10. 1.298 106.392 ↓ 1.5 2,504 1

Finalize HashAggregate (cost=6,312.86..6,329.75 rows=1,689 width=16) (actual time=106.023..106.392 rows=2,504 loops=1)

  • Group Key: usrs_1.user_id
11. 14.895 105.094 ↓ 2.4 3,992 1

Gather (cost=6,118.63..6,304.42 rows=1,689 width=16) (actual time=101.992..105.094 rows=3,992 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
12. 71.420 90.199 ↓ 1.2 1,996 2

Partial HashAggregate (cost=5,118.63..5,135.52 rows=1,689 width=16) (actual time=89.868..90.199 rows=1,996 loops=2)

  • Group Key: usrs_1.user_id
13. 18.779 18.779 ↑ 1.2 118,955 2

Parallel Seq Scan on user_sessions usrs_1 (cost=0.00..4,415.75 rows=140,575 width=16) (actual time=0.008..18.779 rows=118,955 loops=2)

14. 4.820 10.682 ↑ 1.0 20,964 1

Hash (cost=830.20..830.20 rows=21,020 width=29) (actual time=10.682..10.682 rows=20,964 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1516kB
15. 5.862 5.862 ↑ 1.0 20,964 1

Seq Scan on usr (cost=0.00..830.20 rows=21,020 width=29) (actual time=0.011..5.862 rows=20,964 loops=1)

16.          

SubPlan (forHash Join)

17. 0.000 0.000 ↓ 0.0 0 1,293

Result (cost=0.00..5,399.78 rows=238,978 width=8) (actual time=0.000..0.000 rows=0 loops=1,293)

  • One-Time Filter: (usr.group_id = 1105035)
18. 0.016 0.016 ↑ 238,978.0 1 8

Seq Scan on user_sessions (cost=0.00..5,399.78 rows=238,978 width=0) (actual time=0.002..0.002 rows=1 loops=8)