explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l8VK

Settings
# exclusive inclusive rows x rows loops node
1. 11.060 1,736.475 ↑ 4.4 3,602 1

Hash Right Join (cost=238,318.43..239,459.81 rows=15,800 width=344) (actual time=905.681..1,736.475 rows=3,602 loops=1)

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

CTE before_start_time_user_intent

3. 9.962 244.739 ↓ 4.5 4,112 1

Subquery Scan on t (cost=48,035.54..54,461.01 rows=918 width=25) (actual time=163.053..244.739 rows=4,112 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 187,419
4. 49.261 234.777 ↓ 1.0 191,531 1

WindowAgg (cost=48,035.54..52,166.20 rows=183,585 width=41) (actual time=163.051..234.777 rows=191,531 loops=1)

5. 129.745 185.516 ↓ 1.0 191,531 1

Sort (cost=48,035.54..48,494.50 rows=183,585 width=25) (actual time=163.046..185.516 rows=191,531 loops=1)

  • Sort Key: user_intent.device_id, user_intent.last_updated_at DESC
  • Sort Method: external merge Disk: 6,576kB
6. 42.717 55.771 ↓ 1.0 191,531 1

Bitmap Heap Scan on user_intent (cost=9,386.34..27,592.12 rows=183,585 width=25) (actual time=14.287..55.771 rows=191,531 loops=1)

  • Recheck Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (last_updated_at < '2020-06-23 07:00:00'::timestamp without time zone))
  • Heap Blocks: exact=8,922
7. 13.054 13.054 ↓ 1.0 191,531 1

Bitmap Index Scan on user_intent_pkey (cost=0.00..9,340.45 rows=183,585 width=0) (actual time=13.054..13.054 rows=191,531 loops=1)

  • Index Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (last_updated_at < '2020-06-23 07:00:00'::timestamp without time zone))
8.          

CTE user_intent_during_time

9. 2.113 13.134 ↓ 6,043.0 6,043 1

Sort (cost=9,803.47..9,803.48 rows=1 width=25) (actual time=12.683..13.134 rows=6,043 loops=1)

  • Sort Key: user_intent_1.last_updated_at DESC
  • Sort Method: quicksort Memory: 665kB
10. 11.021 11.021 ↓ 6,043.0 6,043 1

Index Scan using user_intent_pkey on user_intent user_intent_1 (cost=0.43..9,803.46 rows=1 width=25) (actual time=0.031..11.021 rows=6,043 loops=1)

  • Index Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (last_updated_at >= '2020-06-23 07:00:00'::timestamp without time zone) AND (last_updated_at <= '2020-06-24 07:00:00'::timestamp without time zone))
11.          

CTE all_user_intents

12. 5.153 265.726 ↓ 11.1 10,155 1

HashAggregate (cost=34.46..43.65 rows=919 width=25) (actual time=264.040..265.726 rows=10,155 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.703 260.573 ↓ 11.1 10,155 1

Append (cost=0.00..27.57 rows=919 width=25) (actual time=163.056..260.573 rows=10,155 loops=1)

14. 245.688 245.688 ↓ 4.5 4,112 1

CTE Scan on before_start_time_user_intent (cost=0.00..18.36 rows=918 width=25) (actual time=163.055..245.688 rows=4,112 loops=1)

15. 14.182 14.182 ↓ 6,043.0 6,043 1

CTE Scan on user_intent_during_time (cost=0.00..0.02 rows=1 width=25) (actual time=12.685..14.182 rows=6,043 loops=1)

16.          

CTE all_user_intents_times

17. 5.448 278.354 ↓ 11.1 10,155 1

WindowAgg (cost=63.61..86.59 rows=919 width=41) (actual time=272.219..278.354 rows=10,155 loops=1)

18. 4.381 272.906 ↓ 11.1 10,155 1

Sort (cost=63.61..65.91 rows=919 width=25) (actual time=272.205..272.906 rows=10,155 loops=1)

  • Sort Key: all_user_intents.device_id, all_user_intents.last_updated_at
  • Sort Method: quicksort Memory: 1,178kB
19. 268.525 268.525 ↓ 11.1 10,155 1

CTE Scan on all_user_intents (cost=0.00..18.38 rows=919 width=25) (actual time=264.042..268.525 rows=10,155 loops=1)

20.          

CTE device_user_intent_seconds

21. 3.038 284.126 ↓ 18.8 3,602 1

HashAggregate (cost=20.68..22.60 rows=192 width=24) (actual time=283.216..284.126 rows=3,602 loops=1)

  • Group Key: all_user_intents_times.device_id
22. 281.088 281.088 ↓ 18.5 8,490 1

CTE Scan on all_user_intents_times (cost=0.00..18.38 rows=460 width=24) (actual time=272.221..281.088 rows=8,490 loops=1)

  • Filter: user_intent_tracking
  • Rows Removed by Filter: 1,665
23.          

CTE latest_device_data

24. 5.928 287.213 ↓ 28.3 2,882 1

Subquery Scan on t_1 (cost=82,796.77..83,462.96 rows=102 width=46) (actual time=228.023..287.213 rows=2,882 loops=1)

  • Filter: (t_1.rn = 1)
  • Rows Removed by Filter: 98,434
25. 29.202 281.285 ↓ 4.9 101,316 1

WindowAgg (cost=82,796.77..83,206.73 rows=20,498 width=68) (actual time=228.021..281.285 rows=101,316 loops=1)

26. 130.555 252.083 ↓ 4.9 101,316 1

Sort (cost=82,796.77..82,848.02 rows=20,498 width=60) (actual time=228.015..252.083 rows=101,316 loops=1)

  • Sort Key: a_device_30_utc.device_id, a_device_30_utc.time_range DESC
  • Sort Method: external merge Disk: 7,040kB
27. 90.646 121.528 ↓ 4.9 101,316 1

Bitmap Heap Scan on a_device_30_utc (cost=1,602.65..81,328.79 rows=20,498 width=60) (actual time=36.852..121.528 rows=101,316 loops=1)

  • Recheck Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (time_range <@ '[""2020-06-23 07:00:00"",""2020-06-24 07:00:00"")'::tsrange))
  • Filter: (event_summary IS NOT NULL)
  • Heap Blocks: exact=36,515
28. 30.882 30.882 ↓ 7.2 147,826 1

Bitmap Index Scan on account_time_range_index (cost=0.00..1,597.53 rows=20,498 width=0) (actual time=30.882..30.882 rows=147,826 loops=1)

  • Index Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (time_range <@ '[""2020-06-23 07:00:00"",""2020-06-24 07:00:00"")'::tsrange))
29.          

CTE device_data

30. 668.370 1,041.245 ↑ 5.7 2,882 1

GroupAggregate (cost=88,683.77..90,426.85 rows=16,458 width=256) (actual time=329.287..1,041.245 rows=2,882 loops=1)

  • Group Key: a_device_30_utc_1.device_id
31. 241.129 372.875 ↓ 4.9 101,316 1

Sort (cost=88,683.77..88,735.02 rows=20,498 width=643) (actual time=328.807..372.875 rows=101,316 loops=1)

  • Sort Key: a_device_30_utc_1.device_id
  • Sort Method: external merge Disk: 74,704kB
32. 101.477 131.746 ↓ 4.9 101,316 1

Bitmap Heap Scan on a_device_30_utc a_device_30_utc_1 (cost=1,602.65..81,328.79 rows=20,498 width=643) (actual time=36.234..131.746 rows=101,316 loops=1)

  • Recheck Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (time_range <@ '[""2020-06-23 07:00:00"",""2020-06-24 07:00:00"")'::tsrange))
  • Heap Blocks: exact=36,515
33. 30.269 30.269 ↓ 7.2 147,826 1

Bitmap Index Scan on account_time_range_index (cost=0.00..1,597.53 rows=20,498 width=0) (actual time=30.269..30.269 rows=147,826 loops=1)

  • Index Cond: ((account_id = '467b4a44-7e32-437f-b4d4-6d4f89283db5'::uuid) AND (time_range <@ '[""2020-06-23 07:00:00"",""2020-06-24 07:00:00"")'::tsrange))
34. 1,149.143 1,149.143 ↑ 5.7 2,882 1

CTE Scan on device_data (cost=0.00..329.16 rows=16,458 width=256) (actual time=329.391..1,149.143 rows=2,882 loops=1)

35. 0.965 576.272 ↓ 18.8 3,602 1

Hash (cost=8.89..8.89 rows=192 width=88) (actual time=576.272..576.272 rows=3,602 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 288kB
36. 1.167 575.307 ↓ 18.8 3,602 1

Hash Left Join (cost=3.32..8.89 rows=192 width=88) (actual time=572.255..575.307 rows=3,602 loops=1)

  • Hash Cond: (device_user_intent_seconds.device_id = latest_device_data.device_id)
37. 285.118 285.118 ↓ 18.8 3,602 1

CTE Scan on device_user_intent_seconds (cost=0.00..3.84 rows=192 width=24) (actual time=283.218..285.118 rows=3,602 loops=1)

38. 0.761 289.022 ↓ 28.3 2,882 1

Hash (cost=2.04..2.04 rows=102 width=80) (actual time=289.022..289.022 rows=2,882 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 226kB
39. 288.261 288.261 ↓ 28.3 2,882 1

CTE Scan on latest_device_data (cost=0.00..2.04 rows=102 width=80) (actual time=228.025..288.261 rows=2,882 loops=1)

Planning time : 0.735 ms
Execution time : 1,768.631 ms