explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xUFx

Settings
# exclusive inclusive rows x rows loops node
1. 0.317 81.537 ↑ 20.8 118 1

Hash Right Join (cost=69,262.63..69,510.18 rows=2,454 width=344) (actual time=56.922..81.537 rows=118 loops=1)

  • Hash Cond: (device_data.device_id = device_user_intent_seconds.device_id)
2.          

CTE before_start_time_user_intent

3. 0.781 20.609 ↓ 7.4 606 1

Subquery Scan on t (cost=18,445.20..19,018.32 rows=82 width=25) (actual time=14.904..20.609 rows=606 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 14,449
4. 4.010 19.828 ↑ 1.1 15,055 1

WindowAgg (cost=18,445.20..18,813.64 rows=16,375 width=41) (actual time=14.903..19.828 rows=15,055 loops=1)

5. 7.855 15.818 ↑ 1.1 15,055 1

Sort (cost=18,445.20..18,486.14 rows=16,375 width=25) (actual time=14.898..15.818 rows=15,055 loops=1)

  • Sort Key: user_intent.device_id, user_intent.last_updated_at DESC
  • Sort Method: quicksort Memory: 1,561kB
6. 6.450 7.963 ↑ 1.1 15,055 1

Bitmap Heap Scan on user_intent (cost=965.12..17,299.01 rows=16,375 width=25) (actual time=1.902..7.963 rows=15,055 loops=1)

  • Recheck Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (last_updated_at < '2020-05-27 07:00:00'::timestamp without time zone))
  • Heap Blocks: exact=3,404
7. 1.513 1.513 ↑ 1.1 15,055 1

Bitmap Index Scan on user_intent_pkey (cost=0.00..961.03 rows=16,375 width=0) (actual time=1.513..1.513 rows=15,055 loops=1)

  • Index Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (last_updated_at < '2020-05-27 07:00:00'::timestamp without time zone))
8.          

CTE user_intent_during_time

9. 0.031 0.828 ↑ 1.0 92 1

Sort (cost=1,382.52..1,382.76 rows=96 width=25) (actual time=0.823..0.828 rows=92 loops=1)

  • Sort Key: user_intent_1.last_updated_at DESC
  • Sort Method: quicksort Memory: 32kB
10. 0.797 0.797 ↑ 1.0 92 1

Index Scan using user_intent_pkey on user_intent user_intent_1 (cost=0.43..1,379.36 rows=96 width=25) (actual time=0.027..0.797 rows=92 loops=1)

  • Index Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (last_updated_at >= '2020-05-27 07:00:00'::timestamp without time zone) AND (last_updated_at <= '2020-05-28 07:00:00'::timestamp without time zone))
11.          

CTE all_user_intents

12. 0.346 21.983 ↓ 3.9 698 1

HashAggregate (cost=6.67..8.46 rows=178 width=25) (actual time=21.892..21.983 rows=698 loops=1)

  • Group Key: before_start_time_user_intent.device_id, before_start_time_user_intent.user_intent_tracking, before_start_time_user_intent.last_updated_at
13. 0.049 21.637 ↓ 3.9 698 1

Append (cost=0.00..5.34 rows=178 width=25) (actual time=14.905..21.637 rows=698 loops=1)

14. 20.744 20.744 ↓ 7.4 606 1

CTE Scan on before_start_time_user_intent (cost=0.00..1.64 rows=82 width=25) (actual time=14.905..20.744 rows=606 loops=1)

15. 0.844 0.844 ↑ 1.0 92 1

CTE Scan on user_intent_during_time (cost=0.00..1.92 rows=96 width=25) (actual time=0.824..0.844 rows=92 loops=1)

16.          

CTE all_user_intents_times

17. 0.396 22.762 ↓ 3.9 698 1

WindowAgg (cost=10.21..14.66 rows=178 width=41) (actual time=22.334..22.762 rows=698 loops=1)

18. 0.205 22.366 ↓ 3.9 698 1

Sort (cost=10.21..10.66 rows=178 width=25) (actual time=22.327..22.366 rows=698 loops=1)

  • Sort Key: all_user_intents.device_id, all_user_intents.last_updated_at
  • Sort Method: quicksort Memory: 79kB
19. 22.161 22.161 ↓ 3.9 698 1

CTE Scan on all_user_intents (cost=0.00..3.56 rows=178 width=25) (actual time=21.893..22.161 rows=698 loops=1)

20.          

CTE device_user_intent_seconds

21. 0.056 22.987 ↓ 1.3 118 1

HashAggregate (cost=4.00..4.89 rows=89 width=24) (actual time=22.967..22.987 rows=118 loops=1)

  • Group Key: all_user_intents_times.device_id
22. 22.931 22.931 ↓ 1.4 124 1

CTE Scan on all_user_intents_times (cost=0.00..3.56 rows=89 width=24) (actual time=22.337..22.931 rows=124 loops=1)

  • Filter: user_intent_tracking
  • Rows Removed by Filter: 574
23.          

CTE latest_device_data

24. 0.299 15.436 ↓ 5.4 158 1

Subquery Scan on t_1 (cost=24,063.81..24,255.04 rows=29 width=46) (actual time=13.379..15.436 rows=158 loops=1)

  • Filter: (t_1.rn = 1)
  • Rows Removed by Filter: 5,736
25. 1.485 15.137 ↓ 1.0 5,894 1

WindowAgg (cost=24,063.81..24,181.49 rows=5,884 width=68) (actual time=13.378..15.137 rows=5,894 loops=1)

26. 6.215 13.652 ↓ 1.0 5,894 1

Sort (cost=24,063.81..24,078.52 rows=5,884 width=60) (actual time=13.375..13.652 rows=5,894 loops=1)

  • Sort Key: a_device_30_utc.device_id, a_device_30_utc.time_range DESC
  • Sort Method: quicksort Memory: 786kB
27. 5.910 7.437 ↓ 1.0 5,894 1

Bitmap Heap Scan on a_device_30_utc (cost=460.86..23,695.39 rows=5,884 width=60) (actual time=1.895..7.437 rows=5,894 loops=1)

  • Recheck Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (time_range <@ '[""2020-05-27 07:00:00"",""2020-05-28 07:00:00"")'::tsrange))
  • Heap Blocks: exact=3,372
28. 1.527 1.527 ↓ 1.0 5,899 1

Bitmap Index Scan on account_time_range_index (cost=0.00..459.39 rows=5,884 width=0) (actual time=1.527..1.527 rows=5,899 loops=1)

  • Index Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (time_range <@ '[""2020-05-27 07:00:00"",""2020-05-28 07:00:00"")'::tsrange))
29.          

CTE device_data

30. 16.753 36.344 ↑ 34.9 158 1

GroupAggregate (cost=24,063.81..24,574.04 rows=5,515 width=256) (actual time=18.264..36.344 rows=158 loops=1)

  • Group Key: a_device_30_utc_1.device_id
31. 12.276 19.591 ↓ 1.0 5,894 1

Sort (cost=24,063.81..24,078.52 rows=5,884 width=643) (actual time=18.062..19.591 rows=5,894 loops=1)

  • Sort Key: a_device_30_utc_1.device_id
  • Sort Method: external merge Disk: 3,760kB
32. 5.750 7.315 ↓ 1.0 5,894 1

Bitmap Heap Scan on a_device_30_utc a_device_30_utc_1 (cost=460.86..23,695.39 rows=5,884 width=643) (actual time=1.943..7.315 rows=5,894 loops=1)

  • Recheck Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (time_range <@ '[""2020-05-27 07:00:00"",""2020-05-28 07:00:00"")'::tsrange))
  • Heap Blocks: exact=3,372
33. 1.565 1.565 ↓ 1.0 5,899 1

Bitmap Index Scan on account_time_range_index (cost=0.00..459.39 rows=5,884 width=0) (actual time=1.565..1.565 rows=5,899 loops=1)

  • Index Cond: ((account_id = 'ce49e5d0-fa63-4993-80a9-35a3b4ed2f43'::uuid) AND (time_range <@ '[""2020-05-27 07:00:00"",""2020-05-28 07:00:00"")'::tsrange))
34. 42.627 42.627 ↑ 34.9 158 1

CTE Scan on device_data (cost=0.00..110.30 rows=5,515 width=256) (actual time=18.314..42.627 rows=158 loops=1)

35. 0.029 38.593 ↓ 1.3 118 1

Hash (cost=3.35..3.35 rows=89 width=88) (actual time=38.593..38.593 rows=118 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
36. 0.034 38.564 ↓ 1.3 118 1

Hash Left Join (cost=0.94..3.35 rows=89 width=88) (actual time=38.497..38.564 rows=118 loops=1)

  • Hash Cond: (device_user_intent_seconds.device_id = latest_device_data.device_id)
37. 23.011 23.011 ↓ 1.3 118 1

CTE Scan on device_user_intent_seconds (cost=0.00..1.78 rows=89 width=24) (actual time=22.968..23.011 rows=118 loops=1)

38. 0.034 15.519 ↓ 5.4 158 1

Hash (cost=0.58..0.58 rows=29 width=80) (actual time=15.519..15.519 rows=158 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
39. 15.485 15.485 ↓ 5.4 158 1

CTE Scan on latest_device_data (cost=0.00..0.58 rows=29 width=80) (actual time=13.381..15.485 rows=158 loops=1)

Planning time : 0.669 ms
Execution time : 83.906 ms