explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yvVF

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=238,283.15..239,424.32 rows=15,797 width=344) (actual rows= loops=)

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

CTE before_start_time_user_intent

3. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=48,033.11..54,458.16 rows=918 width=25) (actual rows= loops=)

  • Filter: (t.rn = 1)
4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=48,033.11..52,163.50 rows=183,573 width=41) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=48,033.11..48,492.04 rows=183,573 width=25) (actual rows= loops=)

  • Sort Key: user_intent.device_id, user_intent.last_updated_at DESC
6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on user_intent (cost=9,386.23..27,590.83 rows=183,573 width=25) (actual rows= loops=)

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

Bitmap Index Scan on user_intent_pkey (cost=0.00..9,340.34 rows=183,573 width=0) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Sort (cost=9,803.34..9,803.34 rows=1 width=25) (actual rows= loops=)

  • Sort Key: user_intent_1.last_updated_at DESC
10. 0.000 0.000 ↓ 0.0

Index Scan using user_intent_pkey on user_intent user_intent_1 (cost=0.43..9,803.33 rows=1 width=25) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

HashAggregate (cost=34.46..43.65 rows=919 width=25) (actual rows= loops=)

  • 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.000 0.000 ↓ 0.0

Append (cost=0.00..27.57 rows=919 width=25) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

CTE Scan on before_start_time_user_intent (cost=0.00..18.36 rows=918 width=25) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on user_intent_during_time (cost=0.00..0.02 rows=1 width=25) (actual rows= loops=)

16.          

CTE all_user_intents_times

17. 0.000 0.000 ↓ 0.0

WindowAgg (cost=63.61..86.59 rows=919 width=41) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=63.61..65.91 rows=919 width=25) (actual rows= loops=)

  • Sort Key: all_user_intents.device_id, all_user_intents.last_updated_at
19. 0.000 0.000 ↓ 0.0

CTE Scan on all_user_intents (cost=0.00..18.38 rows=919 width=25) (actual rows= loops=)

20.          

CTE device_user_intent_seconds

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=20.68..22.60 rows=192 width=24) (actual rows= loops=)

  • Group Key: all_user_intents_times.device_id
22. 0.000 0.000 ↓ 0.0

CTE Scan on all_user_intents_times (cost=0.00..18.38 rows=460 width=24) (actual rows= loops=)

  • Filter: user_intent_tracking
23.          

CTE latest_device_data

24. 0.000 0.000 ↓ 0.0

Subquery Scan on t_1 (cost=82,780.86..83,446.92 rows=102 width=46) (actual rows= loops=)

  • Filter: (t_1.rn = 1)
25. 0.000 0.000 ↓ 0.0

WindowAgg (cost=82,780.86..83,190.74 rows=20,494 width=68) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=82,780.86..82,832.10 rows=20,494 width=60) (actual rows= loops=)

  • Sort Key: a_device_30_utc.device_id, a_device_30_utc.time_range DESC
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on a_device_30_utc (cost=1,602.61..81,313.19 rows=20,494 width=60) (actual rows= loops=)

  • 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)
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on account_time_range_index (cost=0.00..1,597.49 rows=20,494 width=0) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=88,667.86..90,410.60 rows=16,455 width=256) (actual rows= loops=)

  • Group Key: a_device_30_utc_1.device_id
31. 0.000 0.000 ↓ 0.0

Sort (cost=88,667.86..88,719.10 rows=20,494 width=643) (actual rows= loops=)

  • Sort Key: a_device_30_utc_1.device_id
32. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on a_device_30_utc a_device_30_utc_1 (cost=1,602.61..81,313.19 rows=20,494 width=643) (actual rows= loops=)

  • 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))
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on account_time_range_index (cost=0.00..1,597.49 rows=20,494 width=0) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

CTE Scan on device_data (cost=0.00..329.10 rows=16,455 width=256) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=8.89..8.89 rows=192 width=88) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3.32..8.89 rows=192 width=88) (actual rows= loops=)

  • Hash Cond: (device_user_intent_seconds.device_id = latest_device_data.device_id)
37. 0.000 0.000 ↓ 0.0

CTE Scan on device_user_intent_seconds (cost=0.00..3.84 rows=192 width=24) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=2.04..2.04 rows=102 width=80) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

CTE Scan on latest_device_data (cost=0.00..2.04 rows=102 width=80) (actual rows= loops=)