explain.depesz.com

PostgreSQL's explain analyze made readable

Result: geeF : User email activity 50 uids all time, poky

Settings
# exclusive inclusive rows x rows loops node
1. 12,391.453 12,391.453 ↑ 4.0 50 1

CTE Scan on aggregated_event_counts (cost=2,695,438.92..2,695,442.82 rows=200 width=40) (actual time=12,390.954..12,391.453 rows=50 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
  • JIT:
  • Functions: 87
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.206 ms, Inlining 22.522 ms, Optimization 630.845 ms, Emission 391.292 ms, Total 1055.864 ms
2.          

CTE events_deduped

3. 26.522 11,793.686 ↓ 1.7 110,730 1

Unique (cost=2,691,492.77..2,691,747.00 rows=63,558 width=73) (actual time=11,755.458..11,793.686 rows=110,730 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
4. 385.671 11,767.164 ↓ 1.7 111,037 1

Sort (cost=2,691,492.77..2,691,524.55 rows=63,558 width=73) (actual time=11,755.455..11,767.164 rows=111,037 loops=1)

  • Sort Key: message_report_buckets.user_id, message_report_buckets.event_name, message_report_buckets.bucket_hour, message_report_buckets.fingerprint, message_report_buckets.r_track_key, message_report_buckets.event_count, message_report_buckets.colleague_only_email
  • Sort Method: quicksort Memory: 18609kB
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
5. 10,932.133 11,381.493 ↓ 1.7 111,037 1

Bitmap Heap Scan on message_report_buckets (cost=17,211.26..2,690,478.65 rows=63,558 width=73) (actual time=1,499.055..11,381.493 rows=111,037 loops=1)

  • Recheck Cond: (((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{420768,438912}'::integer[]))) OR ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{420864,422400,423936,425472,427008,428544,430080,431616,433152,434688,436224}'::integer[]))) OR ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{437760,438144,438528}'::integer[]))) OR ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{439008,439014}'::integer[]))) OR ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{439020,439021,439022,439023,439024}'::integer[]))))
  • Filter: ((NOT colleague_only_email) AND (NOT starts_with(event_name, 'domain-'::text)) AND ((r_bucket_hour IS NULL) OR ((r_bucket_hour >= 420768) AND (r_bucket_hour <= 439024))) AND (((bucket_hour_count = 96) AND (bucket_hour = ANY ('{420768,438912}'::integer[]))) OR ((bucket_hour_count = 1536) AND (bucket_hour = ANY ('{420864,422400,423936,425472,427008,428544,430080,431616,433152,434688,436224}'::integer[]))) OR ((bucket_hour_count = 384) AND (bucket_hour = ANY ('{437760,438144,438528}'::integer[]))) OR ((bucket_hour_count = 6) AND (bucket_hour = ANY ('{439008,439014}'::integer[]))) OR ((bucket_hour_count = 1) AND (bucket_hour = ANY ('{439020,439021,439022,439023,439024}'::integer[])))) AND (user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])))
  • Rows Removed by Filter: 71879
  • Heap Blocks: exact=26431
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
6. 0.005 449.360 ↓ 0.0 0 1

BitmapOr (cost=17,211.26..17,211.26 rows=1,430,417 width=0) (actual time=449.360..449.360 rows=0 loops=1)

  • Buffers: shared hit=4205 read=1155
  • I/O Timings: read=425.141
7. 74.086 74.086 ↑ 1.4 1,319 1

Bitmap Index Scan on index_message_report_buckets_on_user_id_and_bucket_hour (cost=0.00..215.45 rows=1,868 width=0) (actual time=74.086..74.086 rows=1,319 loops=1)

  • Index Cond: ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{420768,438912}'::integer[])))
  • Buffers: shared hit=266 read=137
  • I/O Timings: read=72.578
8. 323.140 323.140 ↑ 7.3 175,993 1

Bitmap Index Scan on index_message_report_buckets_on_user_id_and_bucket_hour (cost=0.00..14,751.28 rows=1,293,396 width=0) (actual time=323.140..323.140 rows=175,993 loops=1)

  • Index Cond: ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{420864,422400,423936,425472,427008,428544,430080,431616,433152,434688,436224}'::integer[])))
  • Buffers: shared hit=1957 read=932
  • I/O Timings: read=303.302
9. 51.309 51.309 ↑ 23.0 5,604 1

Bitmap Index Scan on index_message_report_buckets_on_user_id_and_bucket_hour (cost=0.00..1,474.57 rows=128,617 width=0) (actual time=51.309..51.309 rows=5,604 loops=1)

  • Index Cond: ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{437760,438144,438528}'::integer[])))
  • Buffers: shared hit=582 read=86
  • I/O Timings: read=49.262
10. 0.363 0.363 ↓ 0.0 0 1

Bitmap Index Scan on index_message_report_buckets_on_user_id_and_bucket_hour (cost=0.00..215.45 rows=1,868 width=0) (actual time=0.363..0.363 rows=0 loops=1)

  • Index Cond: ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{439008,439014}'::integer[])))
  • Buffers: shared hit=400
11. 0.457 0.457 ↓ 0.0 0 1

Bitmap Index Scan on index_message_report_buckets_on_user_id_and_bucket_hour (cost=0.00..538.62 rows=4,669 width=0) (actual time=0.457..0.457 rows=0 loops=1)

  • Index Cond: ((user_id = ANY ('{1026164,1023408,1023422,1023435,1023436,1023540,1023547,1023576,1023583,1023606,1023677,1023707,1023754,1023799,1023807,1023868,1023889,1021947,1021949,1021969,1021977,1027662,1026437,1026511,1026533,1026552,1026598,1026652,1079897,1079947,1079952,1080001,1080014,1080018,1080027,1080395,1080406,1080433,1080477,1077502,1077592,1078166,1078240,1078711,1078755,1079339,1079455,1079472,1079783,1080793}'::integer[])) AND (bucket_hour = ANY ('{439020,439021,439022,439023,439024}'::integer[])))
  • Buffers: shared hit=1000
12.          

CTE event_counts

13. 0.271 12,390.508 ↑ 22.8 313 1

HashAggregate (cost=3,568.05..3,589.47 rows=7,140 width=52) (actual time=12,390.413..12,390.508 rows=313 loops=1)

  • Group Key: events_deduped.event_name, events_deduped.user_id, (sum(events_deduped.event_count)), (count(DISTINCT ((events_deduped.event_name || ':'::text) || events_deduped.r_track_key)))
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
14. 0.046 12,390.237 ↑ 22.8 313 1

Append (cost=1,395.47..3,553.77 rows=7,140 width=52) (actual time=11,878.271..12,390.237 rows=313 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
15. 249.202 12,133.620 ↑ 26.5 240 1

GroupAggregate (cost=1,395.47..1,636.99 rows=6,356 width=52) (actual time=11,878.270..12,133.620 rows=240 loops=1)

  • Group Key: events_deduped.event_name, events_deduped.user_id
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
16. 60.875 11,884.418 ↓ 1.7 110,730 1

Sort (cost=1,395.47..1,427.24 rows=63,558 width=72) (actual time=11,878.235..11,884.418 rows=110,730 loops=1)

  • Sort Key: events_deduped.event_name, events_deduped.user_id
  • Sort Method: quicksort Memory: 18385kB
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
17. 11,823.543 11,823.543 ↓ 1.7 110,730 1

CTE Scan on events_deduped (cost=0.00..381.35 rows=63,558 width=72) (actual time=11,755.472..11,823.543 rows=110,730 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
18. 0.013 178.334 ↑ 6.5 31 1

Subquery Scan on *SELECT* 2 (cost=577.22..583.28 rows=200 width=52) (actual time=46.008..178.334 rows=31 loops=1)

19. 130.862 178.321 ↑ 6.5 31 1

GroupAggregate (cost=577.22..582.58 rows=200 width=48) (actual time=46.006..178.321 rows=31 loops=1)

  • Group Key: events_deduped_1.user_id
20. 25.157 47.459 ↓ 32.3 102,696 1

Sort (cost=577.22..578.80 rows=3,178 width=36) (actual time=41.737..47.459 rows=102,696 loops=1)

  • Sort Key: events_deduped_1.user_id
  • Sort Method: quicksort Memory: 11096kB
21. 22.302 22.302 ↓ 32.3 102,696 1

CTE Scan on events_deduped events_deduped_1 (cost=0.00..540.24 rows=3,178 width=36) (actual time=0.019..22.302 rows=102,696 loops=1)

  • Filter: (event_name = ANY ('{open,domain-open,click,domain-click,file,domain-file,view,domain-view,reply,domain-reply}'::text[]))
  • Rows Removed by Filter: 8034
22. 0.002 14.277 ↑ 18.2 11 1

Subquery Scan on *SELECT* 3 (cost=458.01..461.22 rows=200 width=52) (actual time=14.207..14.277 rows=11 loops=1)

23. 0.075 14.275 ↑ 18.2 11 1

GroupAggregate (cost=458.01..460.52 rows=200 width=48) (actual time=14.205..14.275 rows=11 loops=1)

  • Group Key: events_deduped_2.user_id
24. 0.026 14.200 ↑ 11.8 108 1

Sort (cost=458.01..458.65 rows=1,271 width=36) (actual time=14.194..14.200 rows=108 loops=1)

  • Sort Key: events_deduped_2.user_id
  • Sort Method: quicksort Memory: 33kB
25. 14.174 14.174 ↑ 11.8 108 1

CTE Scan on events_deduped events_deduped_2 (cost=0.00..444.91 rows=1,271 width=36) (actual time=2.138..14.174 rows=108 loops=1)

  • Filter: (event_name = ANY ('{file,domain-file,view,domain-view}'::text[]))
  • Rows Removed by Filter: 110622
26. 0.008 53.605 ↑ 6.4 30 1

Subquery Scan on *SELECT* 4 (cost=419.05..421.25 rows=192 width=52) (actual time=18.283..53.605 rows=30 loops=1)

27. 35.492 53.597 ↑ 6.4 30 1

GroupAggregate (cost=419.05..420.58 rows=192 width=48) (actual time=18.282..53.597 rows=30 loops=1)

  • Group Key: events_deduped_3.user_id
28. 5.797 18.105 ↓ 47.6 30,289 1

Sort (cost=419.05..419.37 rows=636 width=36) (actual time=16.517..18.105 rows=30,289 loops=1)

  • Sort Key: events_deduped_3.user_id
  • Sort Method: quicksort Memory: 3135kB
29. 12.308 12.308 ↓ 47.6 30,289 1

CTE Scan on events_deduped events_deduped_3 (cost=0.00..413.13 rows=636 width=36) (actual time=0.217..12.308 rows=30,289 loops=1)

  • Filter: (event_name = ANY ('{reply,domain-reply}'::text[]))
  • Rows Removed by Filter: 80441
30. 0.002 10.355 ↑ 192.0 1 1

Subquery Scan on *SELECT* 5 (cost=419.05..421.25 rows=192 width=52) (actual time=10.355..10.355 rows=1 loops=1)

31. 0.016 10.353 ↑ 192.0 1 1

GroupAggregate (cost=419.05..420.58 rows=192 width=48) (actual time=10.353..10.353 rows=1 loops=1)

  • Group Key: evts_dedup.user_id
32. 0.006 10.337 ↑ 53.0 12 1

Sort (cost=419.05..419.37 rows=636 width=36) (actual time=10.336..10.337 rows=12 loops=1)

  • Sort Key: evts_dedup.user_id
  • Sort Method: quicksort Memory: 25kB
33. 10.331 10.331 ↑ 53.0 12 1

CTE Scan on events_deduped evts_dedup (cost=0.00..413.13 rows=636 width=36) (actual time=6.169..10.331 rows=12 loops=1)

  • Filter: (event_name = ANY ('{click,domain-click}'::text[]))
  • Rows Removed by Filter: 110718
34.          

CTE flattened_event_counts

35. 12,390.653 12,390.653 ↑ 22.8 313 1

CTE Scan on event_counts (cost=0.00..51.77 rows=7,140 width=44) (actual time=12,390.416..12,390.653 rows=313 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
36.          

CTE aggregated_event_counts

37. 0.191 12,390.938 ↑ 4.0 50 1

HashAggregate (cost=49.98..50.68 rows=200 width=36) (actual time=12,390.926..12,390.938 rows=50 loops=1)

  • Group Key: flattened_event_counts.user_id
  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
38. 12,390.747 12,390.747 ↑ 22.8 313 1

CTE Scan on flattened_event_counts (cost=0.00..42.84 rows=7,140 width=44) (actual time=12,390.418..12,390.747 rows=313 loops=1)

  • Buffers: shared hit=5173 read=26618
  • I/O Timings: read=9889.352
Planning time : 0.892 ms
Execution time : 12,408.110 ms