explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mV3e : top chart

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,018.227 ↓ 0.0 0 1

Limit (cost=0.00..0.00 rows=0 width=0) (actual time=1,018.227..1,018.227 rows=0 loops=1)

2. 0.021 1,018.226 ↓ 0.0 0 1

Sort (cost=0.00..0.00 rows=0 width=0) (actual time=1,018.225..1,018.226 rows=0 loops=1)

  • Sort Key: pg_catalog.sum((pg_catalog.sum(remote_scan.new_free))) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.002 1,018.205 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=1,018.205..1,018.205 rows=0 loops=1)

  • Group Key: remote_scan.product_id
4. 824.911 1,018.203 ↓ 0.0 0 1

Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) (actual time=1,018.203..1,018.203 rows=0 loops=1)

  • Task Count: 256
  • Tasks Shown: One of 256
  • -> Task
  • Node: host=ec2-dca-aa-s-tidb-51 port=5432 dbname=aa_citus_db
5. 19.557 193.292 ↓ 0.0 0 1

Gather (cost=114.99..115.00 rows=1 width=152) (actual time=181.010..193.292 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Single Copy: true
6. 0.001 173.735 ↓ 0.0 0 1

Limit (cost=114.99..115.00 rows=1 width=152) (actual time=173.735..173.735 rows=0 loops=1)

7. 0.050 173.734 ↓ 0.0 0 1

Sort (cost=114.99..115.00 rows=1 width=152) (actual time=173.734..173.734 rows=0 loops=1)

  • Sort Key: (sum(CASE WHEN (t.date >= '2017-01-01'::date) THEN t.est_free_app_download ELSE '0'::bigint END)) DESC
  • Worker 0: Sort Method: quicksort Memory: 25kB
8. 0.002 173.684 ↓ 0.0 0 1

GroupAggregate (cost=114.92..114.98 rows=1 width=152) (actual time=173.684..173.684 rows=0 loops=1)

  • Group Key: t.product_id
9. 0.019 173.682 ↓ 0.0 0 1

Sort (cost=114.92..114.93 rows=1 width=36) (actual time=173.682..173.682 rows=0 loops=1)

  • Sort Key: t.product_id
  • Worker 0: Sort Method: quicksort Memory: 25kB
10. 12.766 173.663 ↓ 0.0 0 1

Hash Join (cost=8.50..114.91 rows=1 width=36) (actual time=173.663..173.663 rows=0 loops=1)

  • Hash Cond: ((t.product_id = c.product_id) AND ((t.device_code)::text = (c.device_code)::text))
11. 9.808 160.357 ↓ 5,831.8 139,963 1

Append (cost=0.55..106.77 rows=24 width=166) (actual time=0.072..160.357 rows=139,963 loops=1)

12. 6.900 6.900 ↓ 5,799.0 5,799 1

Index Only Scan using store_int_metrics_v5_201609_country_code_device_f7ce6c4c_108152 on store_int_metrics_v5_201609_108152 t (cost=0.55..4.45 rows=1 width=166) (actual time=0.072..6.900 rows=5,799 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5799
13. 6.946 6.946 ↓ 6,108.0 6,108 1

Index Only Scan using store_int_metrics_v5_201610_country_code_device_bd1eb2ac_107896 on store_int_metrics_v5_201610_107896 t_1 (cost=0.55..4.45 rows=1 width=166) (actual time=0.083..6.946 rows=6,108 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6108
14. 5.722 5.722 ↓ 5,198.0 5,198 1

Index Only Scan using store_int_metrics_v5_201611_country_code_device_a69b2ea2_107640 on store_int_metrics_v5_201611_107640 t_2 (cost=0.55..4.44 rows=1 width=166) (actual time=0.060..5.722 rows=5,198 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5198
15. 7.110 7.110 ↓ 6,380.0 6,380 1

Index Only Scan using store_int_metrics_v5_201612_country_code_device_4d469685_107384 on store_int_metrics_v5_201612_107384 t_3 (cost=0.55..4.45 rows=1 width=166) (actual time=0.077..7.110 rows=6,380 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6380
16. 6.533 6.533 ↓ 5,959.0 5,959 1

Index Only Scan using store_int_metrics_v5_201701_country_code_device_220dff56_107128 on store_int_metrics_v5_201701_107128 t_4 (cost=0.55..4.45 rows=1 width=166) (actual time=0.068..6.533 rows=5,959 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5959
17. 6.176 6.176 ↓ 5,537.0 5,537 1

Index Only Scan using store_int_metrics_v5_201702_country_code_device_7b216c58_106872 on store_int_metrics_v5_201702_106872 t_5 (cost=0.55..4.45 rows=1 width=166) (actual time=0.056..6.176 rows=5,537 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5537
18. 6.402 6.402 ↓ 5,853.0 5,853 1

Index Only Scan using store_int_metrics_v5_201703_country_code_device_60e59845_106616 on store_int_metrics_v5_201703_106616 t_6 (cost=0.55..4.45 rows=1 width=166) (actual time=0.060..6.402 rows=5,853 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5853
19. 6.486 6.486 ↓ 5,830.0 5,830 1

Index Only Scan using store_int_metrics_v5_201704_country_code_device_49ff5c5c_106360 on store_int_metrics_v5_201704_106360 t_7 (cost=0.55..4.45 rows=1 width=166) (actual time=0.060..6.486 rows=5,830 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5830
20. 6.816 6.816 ↓ 6,425.0 6,425 1

Index Only Scan using store_int_metrics_v5_201705_country_code_device_26d5fcc6_106104 on store_int_metrics_v5_201705_106104 t_8 (cost=0.55..4.45 rows=1 width=166) (actual time=0.053..6.816 rows=6,425 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6425
21. 5.943 5.943 ↓ 5,492.0 5,492 1

Index Only Scan using store_int_metrics_v5_201706_country_code_device_0aae1be3_105848 on store_int_metrics_v5_201706_105848 t_9 (cost=0.55..4.45 rows=1 width=166) (actual time=0.060..5.943 rows=5,492 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5492
22. 6.829 6.829 ↓ 6,400.0 6,400 1

Index Only Scan using store_int_metrics_v5_201707_country_code_device_fbb6902c_105592 on store_int_metrics_v5_201707_105592 t_10 (cost=0.55..4.45 rows=1 width=166) (actual time=0.061..6.829 rows=6,400 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6400
23. 6.358 6.358 ↓ 6,089.0 6,089 1

Index Only Scan using store_int_metrics_v5_201708_country_code_device_c10e283c_105336 on store_int_metrics_v5_201708_105336 t_11 (cost=0.55..4.45 rows=1 width=166) (actual time=0.058..6.358 rows=6,089 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6089
24. 6.124 6.124 ↓ 5,745.0 5,745 1

Index Only Scan using store_int_metrics_v5_201709_country_code_device_54357bcf_105080 on store_int_metrics_v5_201709_105080 t_12 (cost=0.55..4.45 rows=1 width=166) (actual time=0.059..6.124 rows=5,745 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5745
25. 6.011 6.011 ↓ 5,918.0 5,918 1

Index Only Scan using store_int_metrics_v5_201710_country_code_device_b4ea8bb2_104824 on store_int_metrics_v5_201710_104824 t_13 (cost=0.55..4.45 rows=1 width=166) (actual time=0.055..6.011 rows=5,918 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5918
26. 5.563 5.563 ↓ 5,428.0 5,428 1

Index Only Scan using store_int_metrics_v5_201711_country_code_device_6933a66b_104568 on store_int_metrics_v5_201711_104568 t_14 (cost=0.55..4.44 rows=1 width=166) (actual time=0.058..5.563 rows=5,428 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5428
27. 6.062 6.062 ↓ 6,030.0 6,030 1

Index Only Scan using store_int_metrics_v5_201712_country_code_device_652b1e42_104312 on store_int_metrics_v5_201712_104312 t_15 (cost=0.55..4.45 rows=1 width=166) (actual time=0.051..6.062 rows=6,030 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6030
28. 6.127 6.127 ↓ 6,075.0 6,075 1

Index Only Scan using store_int_metrics_v5_201801_country_code_device_46e66739_104056 on store_int_metrics_v5_201801_104056 t_16 (cost=0.55..4.45 rows=1 width=166) (actual time=0.055..6.127 rows=6,075 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6075
29. 5.837 5.837 ↓ 5,604.0 5,604 1

Index Only Scan using store_int_metrics_v5_201802_country_code_device_84bc9aae_103800 on store_int_metrics_v5_201802_103800 t_17 (cost=0.55..4.44 rows=1 width=166) (actual time=0.058..5.837 rows=5,604 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5604
30. 6.528 6.528 ↓ 6,109.0 6,109 1

Index Only Scan using store_int_metrics_v5_201803_country_code_device_21d78d3f_103544 on store_int_metrics_v5_201803_103544 t_18 (cost=0.55..4.45 rows=1 width=166) (actual time=0.051..6.528 rows=6,109 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 6109
31. 5.919 5.919 ↓ 5,496.0 5,496 1

Index Only Scan using store_int_metrics_v5_201804_country_code_device_b53822f1_103288 on store_int_metrics_v5_201804_103288 t_19 (cost=0.55..4.44 rows=1 width=166) (actual time=0.063..5.919 rows=5,496 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5496
32. 6.229 6.229 ↓ 5,669.0 5,669 1

Index Only Scan using store_int_metrics_v5_201805_country_code_device_830f57a6_103032 on store_int_metrics_v5_201805_103032 t_20 (cost=0.55..4.45 rows=1 width=166) (actual time=0.054..6.229 rows=5,669 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5669
33. 5.895 5.895 ↓ 5,481.0 5,481 1

Index Only Scan using store_int_metrics_v5_201806_country_code_device_c8bb4113_102776 on store_int_metrics_v5_201806_102776 t_21 (cost=0.55..4.44 rows=1 width=166) (actual time=0.062..5.895 rows=5,481 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5481
34. 5.720 5.720 ↓ 5,383.0 5,383 1

Index Only Scan using store_int_metrics_v5_201807_country_code_device_c4b8bb70_102520 on store_int_metrics_v5_201807_102520 t_22 (cost=0.55..4.44 rows=1 width=166) (actual time=0.062..5.720 rows=5,383 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5383
35. 6.313 6.313 ↓ 5,955.0 5,955 1

Index Only Scan using store_int_metrics_v5_201808_country_code_device_c3fa2080_102264 on store_int_metrics_v5_201808_102264 t_23 (cost=0.55..4.45 rows=1 width=166) (actual time=0.057..6.313 rows=5,955 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (device_code = ANY ('{ios-phone,ios-tablet}'::text[])) AND (date >= '2016-08-01'::date) AND (date <= '2018-08-31'::date) AND (est_free_app_download IS NOT NULL))
  • Heap Fetches: 5955
36. 0.016 0.540 ↓ 19.3 58 1

Hash (cost=7.91..7.91 rows=3 width=138) (actual time=0.539..0.540 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
37. 0.005 0.524 ↓ 19.3 58 1

Append (cost=0.41..7.91 rows=3 width=138) (actual time=0.091..0.524 rows=58 loops=1)

38. 0.452 0.452 ↓ 58.0 58 1

Index Only Scan using store_int_category_v5_android_country_code_cate_288e9082_109432 on store_int_category_v5_android_all_109432 c (cost=0.41..2.63 rows=1 width=138) (actual time=0.091..0.452 rows=58 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (category_id = 400001))
  • Heap Fetches: 58
39. 0.035 0.035 ↓ 0.0 0 1

Index Only Scan using store_int_category_v5_ios_pho_country_code_cate_2f5b666e_108920 on store_int_category_v5_ios_phone_108920 c_1 (cost=0.41..2.63 rows=1 width=138) (actual time=0.035..0.035 rows=0 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (category_id = 400001))
  • Heap Fetches: 0
40. 0.032 0.032 ↓ 0.0 0 1

Index Only Scan using store_int_category_v5_ios_tab_country_code_cate_c11aa7e5_109176 on store_int_category_v5_ios_tablet_109176 c_2 (cost=0.41..2.63 rows=1 width=138) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: ((country_code = 'US'::bpchar) AND (category_id = 400001))
  • Heap Fetches: 0
Planning time : 28.613 ms
Execution time : 1,018.607 ms