explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jrHo

Settings
# exclusive inclusive rows x rows loops node
1. 12.982 1,527.352 ↑ 4.4 3,602 1

Hash Right Join (cost=238,318.42..239,459.79 rows=15,800 width=344) (actual time=990.139..1,527.352 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. 10.775 292.227 ↓ 4.5 4,112 1

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

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

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

5. 157.249 227.661 ↓ 1.0 191,531 1

Sort (cost=48,035.54..48,494.50 rows=183,585 width=25) (actual time=202.515..227.661 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. 55.372 70.412 ↓ 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=16.571..70.412 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. 15.040 15.040 ↓ 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=15.040..15.040 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. 11.859 11.859 ↓ 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.033..11.859 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))
10.          

CTE all_user_intents

11. 5.860 313.400 ↓ 11.1 10,155 1

HashAggregate (cost=34.46..43.65 rows=919 width=25) (actual time=311.518..313.400 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
12. 0.768 307.540 ↓ 11.1 10,155 1

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

13. 293.286 293.286 ↓ 4.5 4,112 1

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

14. 13.486 13.486 ↓ 6,043.0 6,043 1

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

15.          

CTE all_user_intents_times

16. 5.829 327.120 ↓ 11.1 10,155 1

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

17. 4.868 321.291 ↓ 11.1 10,155 1

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

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

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

19.          

CTE device_user_intent_seconds

20. 2.980 332.939 ↓ 18.8 3,602 1

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

  • Group Key: all_user_intents_times.device_id
21. 329.959 329.959 ↓ 18.5 8,490 1

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

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

CTE latest_device_data

23. 6.280 311.223 ↓ 28.3 2,882 1

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

  • Filter: (t_1.rn = 1)
  • Rows Removed by Filter: 98,434
24. 32.755 304.943 ↓ 4.9 101,316 1

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

25. 141.243 272.188 ↓ 4.9 101,316 1

Sort (cost=82,796.77..82,848.02 rows=20,498 width=60) (actual time=243.363..272.188 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
26. 96.890 130.945 ↓ 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=40.361..130.945 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
27. 34.055 34.055 ↓ 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=34.055..34.055 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))
28.          

CTE device_data

29. 358.546 745.915 ↑ 5.7 2,882 1

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

  • Group Key: a_device_30_utc_1.device_id
30. 244.570 387.369 ↓ 4.9 101,316 1

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

  • Sort Key: a_device_30_utc_1.device_id
  • Sort Method: external merge Disk: 74,704kB
31. 108.417 142.799 ↓ 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=40.849..142.799 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
32. 34.382 34.382 ↓ 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=34.382..34.382 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))
33. 864.735 864.735 ↑ 5.7 2,882 1

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

34. 0.892 649.635 ↓ 18.8 3,602 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 288kB
35. 1.036 648.743 ↓ 18.8 3,602 1

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

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

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

37. 1.398 313.888 ↓ 28.3 2,882 1

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

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

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

Planning time : 0.695 ms
Execution time : 1,602.794 ms