explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ERxm

Settings
# exclusive inclusive rows x rows loops node
1. 0.442 1,576.756 ↑ 20.8 118 1

Hash Right Join (cost=69,262.63..69,510.18 rows=2,454 width=344) (actual time=1,325.323..1,576.756 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.770 20.443 ↓ 7.4 606 1

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

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

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

5. 7.921 15.690 ↑ 1.1 15,055 1

Sort (cost=18,445.20..18,486.14 rows=16,375 width=25) (actual time=14.743..15.690 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.298 7.769 ↑ 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.887..7.769 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.471 1.471 ↑ 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.471..1.471 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.033 0.881 ↑ 1.0 92 1

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

  • Sort Key: user_intent_1.last_updated_at DESC
  • Sort Method: quicksort Memory: 32kB
10. 0.848 0.848 ↑ 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.029..0.848 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.343 21.874 ↓ 3.9 698 1

HashAggregate (cost=6.67..8.46 rows=178 width=25) (actual time=21.780..21.874 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.048 21.531 ↓ 3.9 698 1

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

14. 20.585 20.585 ↓ 7.4 606 1

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

15. 0.898 0.898 ↑ 1.0 92 1

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

16.          

CTE all_user_intents_times

17. 0.361 22.633 ↓ 3.9 698 1

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

18. 0.207 22.272 ↓ 3.9 698 1

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

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

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

20.          

CTE device_user_intent_seconds

21. 0.055 22.844 ↓ 1.3 118 1

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

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

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

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

CTE latest_device_data

24. 0.310 1,280.616 ↓ 5.4 158 1

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

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

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

26. 8.117 1,278.765 ↓ 1.0 5,894 1

Sort (cost=24,063.81..24,078.52 rows=5,884 width=60) (actual time=1,278.445..1,278.765 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. 1,199.805 1,270.648 ↓ 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=71.913..1,270.648 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))
  • Filter: (event_summary IS NOT NULL)
  • Heap Blocks: exact=3,372
28. 70.843 70.843 ↓ 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=70.843..70.843 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. 244.738 265.731 ↑ 34.9 158 1

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

  • Group Key: a_device_30_utc_1.device_id
31. 12.689 20.993 ↓ 1.0 5,894 1

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

  • Sort Key: a_device_30_utc_1.device_id
  • Sort Method: external merge Disk: 3,760kB
32. 6.671 8.304 ↓ 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=2.006..8.304 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.633 1.633 ↓ 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.633..1.633 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. 272.662 272.662 ↑ 34.9 158 1

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

35. 0.027 1,303.652 ↓ 1.3 118 1

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

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

Hash Left Join (cost=0.94..3.35 rows=89 width=88) (actual time=1,303.553..1,303.625 rows=118 loops=1)

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

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

38. 0.049 1,280.716 ↓ 5.4 158 1

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

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

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

Planning time : 0.685 ms
Execution time : 1,579.181 ms