explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YXAyD

Settings
# exclusive inclusive rows x rows loops node
1. 33.636 48,689.236 ↑ 21,773.0 29,619 1

WindowAgg (cost=271,912,727.59..323,504,192.15 rows=644,893,307 width=580) (actual time=48,633.176..48,689.236 rows=29,619 loops=1)

2.          

CTE pre

3. 46,854.967 47,724.488 ↑ 1.9 34,286 1

HashAggregate (cost=141,157.90..153,547.90 rows=66,080 width=366) (actual time=1,114.939..47,724.488 rows=34,286 loops=1)

  • Group Key: au.company, au.division, au.name, au.email, au.actor_id, au.type_user_id, (a.last_sign_in_at)::date
4. 110.089 869.521 ↓ 3.7 242,338 1

Hash Right Join (cost=127,651.97..139,671.10 rows=66,080 width=182) (actual time=699.740..869.521 rows=242,338 loops=1)

  • Hash Cond: (ad.actor_id = a.id)
5. 59.823 59.823 ↓ 1.0 201,572 1

Seq Scan on actor_device ad (cost=0.00..10,438.52 rows=201,229 width=12) (actual time=0.009..59.823 rows=201,572 loops=1)

  • Filter: ((platform)::text <> ALL ('{iOS,""iPhone OS"",IOS,ios}'::text[]))
  • Rows Removed by Filter: 9,531
6. 14.057 699.609 ↓ 1.0 34,333 1

Hash (cost=127,230.08..127,230.08 rows=33,751 width=182) (actual time=699.609..699.609 rows=34,333 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 8,511kB
7. 13.669 685.552 ↓ 1.0 34,333 1

Hash Join (cost=55,260.53..127,230.08 rows=33,751 width=182) (actual time=592.785..685.552 rows=34,333 loops=1)

  • Hash Cond: (au.actor_id = a.id)
8. 177.051 177.051 ↓ 1.0 34,333 1

Seq Scan on all_users au (cost=0.00..71,880.96 rows=33,751 width=170) (actual time=97.067..177.051 rows=34,333 loops=1)

  • Filter: ((deleted_at IS NULL) AND (type_user_id = ANY ('{88,89,2,5,40}'::integer[])))
  • Rows Removed by Filter: 49,046
9. 20.699 494.832 ↑ 1.0 101,616 1

Hash (cost=53,975.79..53,975.79 rows=102,779 width=12) (actual time=494.831..494.832 rows=101,616 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,933kB
10. 474.133 474.133 ↑ 1.0 101,616 1

Seq Scan on actor a (cost=0.00..53,975.79 rows=102,779 width=12) (actual time=0.008..474.133 rows=101,616 loops=1)

11.          

CTE actor_names

12. 29.361 141.831 ↓ 1.5 26,197 1

HashAggregate (cost=72,218.59..72,398.41 rows=17,982 width=72) (actual time=130.558..141.831 rows=26,197 loops=1)

  • Group Key: all_users.actor_id
13. 112.470 112.470 ↓ 1.0 34,359 1

Seq Scan on all_users (cost=0.00..71,880.96 rows=33,763 width=96) (actual time=0.152..112.470 rows=34,359 loops=1)

  • Filter: (type_user_id = ANY ('{88,89,2,5,40}'::integer[]))
  • Rows Removed by Filter: 49,020
14.          

CTE count_devices

15. 168.692 445.012 ↓ 1.9 42,306 1

GroupAggregate (cost=0.42..29,646.28 rows=21,709 width=12) (actual time=0.134..445.012 rows=42,306 loops=1)

  • Group Key: actor_device.actor_id
16. 276.320 276.320 ↑ 1.0 211,103 1

Index Scan using index_actor_device_on_actor_id on actor_device (cost=0.42..28,371.35 rows=211,568 width=21) (actual time=0.016..276.320 rows=211,103 loops=1)

17. 20.359 48,655.600 ↑ 21,773.0 29,619 1

GroupAggregate (cost=271,657,135.01..299,065,100.56 rows=644,893,307 width=276) (actual time=48,633.132..48,655.600 rows=29,619 loops=1)

  • Group Key: p.omsu, p.actor_id, p.email, p.last_sign_in_at, p.version, an.name, cd.some_count, bo.id
18. 76.239 48,635.241 ↑ 18,809.2 34,286 1

Sort (cost=271,657,135.01..273,269,368.28 rows=644,893,307 width=276) (actual time=48,633.109..48,635.241 rows=34,286 loops=1)

  • Sort Key: p.omsu, p.actor_id, p.email, p.last_sign_in_at, p.version, an.name, cd.some_count, bo.id
  • Sort Method: quicksort Memory: 11,773kB
19. 14.435 48,559.002 ↑ 18,809.2 34,286 1

Merge Right Join (cost=10,708.29..9,773,665.64 rows=644,893,307 width=276) (actual time=48,499.593..48,559.002 rows=34,286 loops=1)

  • Merge Cond: (cd.actor_id = p.actor_id)
20. 12.755 469.406 ↓ 1.9 42,306 1

Sort (cost=1,997.88..2,052.15 rows=21,709 width=12) (actual time=465.874..469.406 rows=42,306 loops=1)

  • Sort Key: cd.actor_id
  • Sort Method: quicksort Memory: 3,520kB
21. 456.651 456.651 ↓ 1.9 42,306 1

CTE Scan on count_devices cd (cost=0.00..434.18 rows=21,709 width=12) (actual time=0.135..456.651 rows=42,306 loops=1)

22. 8.849 48,075.161 ↑ 173.3 34,286 1

Materialize (cost=8,710.41..113,012.74 rows=5,941,253 width=268) (actual time=48,033.689..48,075.161 rows=34,286 loops=1)

23. 19.169 48,066.312 ↑ 173.3 34,286 1

Merge Left Join (cost=8,710.41..98,159.61 rows=5,941,253 width=268) (actual time=48,033.684..48,066.312 rows=34,286 loops=1)

  • Merge Cond: (p.actor_id = an.actor_id)
24. 46.772 47,872.162 ↑ 1.9 34,286 1

Sort (cost=7,079.96..7,245.16 rows=66,080 width=236) (actual time=47,863.338..47,872.162 rows=34,286 loops=1)

  • Sort Key: p.actor_id
  • Sort Method: quicksort Memory: 9,718kB
25. 23.559 47,825.390 ↑ 1.9 34,286 1

Hash Left Join (cost=5.46..1,789.62 rows=66,080 width=236) (actual time=1,115.020..47,825.390 rows=34,286 loops=1)

  • Hash Cond: (p.omsu = (bo.name)::text)
26. 47,801.770 47,801.770 ↑ 1.9 34,286 1

CTE Scan on pre p (cost=0.00..1,321.60 rows=66,080 width=232) (actual time=1,114.943..47,801.770 rows=34,286 loops=1)

27. 0.031 0.061 ↑ 1.0 65 1

Hash (cost=4.65..4.65 rows=65 width=54) (actual time=0.061..0.061 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
28. 0.030 0.030 ↑ 1.0 65 1

Seq Scan on blago_omsu bo (cost=0.00..4.65 rows=65 width=54) (actual time=0.009..0.030 rows=65 loops=1)

29. 16.337 174.981 ↓ 1.9 34,297 1

Sort (cost=1,630.45..1,675.41 rows=17,982 width=36) (actual time=170.331..174.981 rows=34,297 loops=1)

  • Sort Key: an.actor_id
  • Sort Method: quicksort Memory: 3,259kB
30. 158.644 158.644 ↓ 1.5 26,197 1

CTE Scan on actor_names an (cost=0.00..359.64 rows=17,982 width=36) (actual time=130.567..158.644 rows=26,197 loops=1)

Planning time : 1.896 ms
Execution time : 48,705.723 ms