explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UwAS

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 64,078.079 ↓ 0.0 0 1

GroupAggregate (cost=4,180,135.51..4,180,140.40 rows=163 width=81) (actual time=64,078.079..64,078.079 rows=0 loops=1)

  • Group Key: dv.venue_id, (((date_trunc('hour'::text, fc.timestamp_start)))::date)
2. 0.007 64,078.079 ↓ 0.0 0 1

Sort (cost=4,180,135.51..4,180,135.91 rows=163 width=53) (actual time=64,078.079..64,078.079 rows=0 loops=1)

  • Sort Key: dv.venue_id, (((date_trunc('hour'::text, fc.timestamp_start)))::date)
  • Sort Method: quicksort Memory: 25kB
3. 0.000 64,078.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,180,027.13..4,180,129.52 rows=163 width=53) (actual time=64,078.072..64,078.072 rows=0 loops=1)

  • Join Filter: (((ph.state = dv.state_abbrev) OR (ph.state IS NULL)) AND (ph.country = dv.country))
4. 0.001 64,078.072 ↓ 0.0 0 1

Merge Left Join (cost=4,180,026.85..4,180,066.61 rows=163 width=65) (actual time=64,078.072..64,078.072 rows=0 loops=1)

  • Merge Cond: ((dv.country = sh.country) AND ((((date_trunc('hour'::text, fc.timestamp_start)))::date) = sh.date))
  • Join Filter: ((sh.state = dv.state_abbrev) OR (sh.state IS NULL))
5. 0.005 64,078.071 ↓ 0.0 0 1

Sort (cost=4,179,745.55..4,179,745.96 rows=163 width=65) (actual time=64,078.071..64,078.071 rows=0 loops=1)

  • Sort Key: dv.country, (((date_trunc('hour'::text, fc.timestamp_start)))::date)
  • Sort Method: quicksort Memory: 25kB
6. 227.235 64,078.066 ↓ 0.0 0 1

Hash Join (cost=4,171,098.70..4,179,739.56 rows=163 width=65) (actual time=64,078.066..64,078.066 rows=0 loops=1)

  • Hash Cond: (dci.counter_id = dc.counter_id)
7. 744.135 63,850.817 ↓ 22.4 3,525,322 1

Hash Join (cost=4,171,090.08..4,179,140.28 rows=157,077 width=65) (actual time=56,246.720..63,850.817 rows=3,525,322 loops=1)

  • Hash Cond: (fc.venue_id = dv.venue_id)
8. 2,804.242 63,106.435 ↓ 22.4 3,525,322 1

GroupAggregate (cost=4,171,056.62..4,175,376.24 rows=157,077 width=25) (actual time=56,246.462..63,106.435 rows=3,525,322 loops=1)

  • Group Key: fc.venue_id, dci.counter_id, (date_trunc('hour'::text, fc.timestamp_start)), dci.is_entry
9. 22,759.896 60,302.193 ↓ 91.2 14,324,977 1

Sort (cost=4,171,056.62..4,171,449.31 rows=157,077 width=21) (actual time=56,246.452..60,302.193 rows=14,324,977 loops=1)

  • Sort Key: fc.venue_id, dci.counter_id, (date_trunc('hour'::text, fc.timestamp_start)), dci.is_entry
  • Sort Method: external merge Disk: 476,192kB
10. 7,491.166 37,542.297 ↓ 91.2 14,324,977 1

Hash Join (cost=3,390,177.97..4,154,276.50 rows=157,077 width=21) (actual time=30,059.267..37,542.297 rows=14,324,977 loops=1)

  • Hash Cond: ((dci.count_sensor_id = fc.count_sensor_id) AND (dci.counter_name = fc.counter_name))
11. 0.884 0.884 ↑ 1.0 3,804 1

Seq Scan on dim_counter_item dci (cost=0.00..98.04 rows=3,804 width=21) (actual time=0.003..0.884 rows=3,804 loops=1)

12. 4,035.564 30,050.247 ↑ 2.3 13,968,052 1

Hash (cost=2,686,259.95..2,686,259.95 rows=32,236,668 width=32) (actual time=30,050.247..30,050.247 rows=13,968,052 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 4,096 (originally 1024) Memory Usage: 3,585kB
13. 920.788 26,014.683 ↑ 2.3 13,968,052 1

Append (cost=0.00..2,686,259.95 rows=32,236,668 width=32) (actual time=0.117..26,014.683 rows=13,968,052 loops=1)

14. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on fact_count fc (cost=0.00..0.00 rows=1 width=52) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
15. 2,827.516 2,827.516 ↑ 1.6 2,111,996 1

Seq Scan on fact_count_190 fc_1 (cost=0.00..284,996.24 rows=3,416,062 width=32) (actual time=0.117..2,827.516 rows=2,111,996 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 8,136,334
16. 2,062.685 2,062.685 ↑ 1.8 1,476,794 1

Seq Scan on fact_count_174 fc_2 (cost=0.00..222,965.46 rows=2,672,542 width=32) (actual time=0.132..2,062.685 rows=1,476,794 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 6,540,918
17. 1,465.688 1,465.688 ↑ 140.8 14,516 1

Seq Scan on fact_count_180 fc_3 (cost=0.00..170,541.36 rows=2,044,159 width=32) (actual time=0.139..1,465.688 rows=14,516 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 6,118,018
18. 41.318 41.318 ↑ 2.8 19,722 1

Seq Scan on fact_count_3044 fc_4 (cost=0.00..4,469.42 rows=55,494 width=27) (actual time=0.112..41.318 rows=19,722 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 146,748
19. 22.874 22.874 ↑ 1.8 16,836 1

Seq Scan on fact_count_2922 fc_5 (cost=0.00..2,423.88 rows=30,093 width=27) (actual time=0.101..22.874 rows=16,836 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 73,384
20. 1,754.323 1,754.323 ↑ 1.9 1,146,762 1

Seq Scan on fact_count_191 fc_6 (cost=0.00..177,408.11 rows=2,126,459 width=32) (actual time=0.103..1,754.323 rows=1,146,762 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 5,232,798
21. 2,444.559 2,444.559 ↓ 1.3 3,577,704 1

Seq Scan on fact_count_176 fc_7 (cost=0.00..229,275.03 rows=2,748,172 width=32) (actual time=0.118..2,444.559 rows=3,577,704 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 4,666,810
22. 20.664 20.664 ↑ 2.7 10,156 1

Seq Scan on fact_count_3045 fc_8 (cost=0.00..2,236.19 rows=27,756 width=27) (actual time=0.124..20.664 rows=10,156 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 73,160
23. 4,576.469 4,576.469 ↑ 1.0 5,394,378 1

Seq Scan on fact_count_172 fc_9 (cost=0.00..452,530.47 rows=5,424,180 width=32) (actual time=0.106..4,576.469 rows=5,394,378 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 10,878,356
24. 2,253.035 2,253.035 ↑ 19.1 163,002 1

Seq Scan on fact_count_183 fc_10 (cost=0.00..259,768.90 rows=3,113,674 width=32) (actual time=0.134..2,253.035 rows=163,002 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 9,178,114
25. 22.512 22.512 ↑ 1.9 15,554 1

Seq Scan on fact_count_2923 fc_11 (cost=0.00..2,386.56 rows=29,630 width=27) (actual time=0.119..22.512 rows=15,554 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 73,276
26. 51.791 51.791 ↑ 7.6 9,406 1

Seq Scan on fact_count_4517 fc_12 (cost=0.00..5,761.06 rows=71,525 width=25) (actual time=0.110..51.791 rows=9,406 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 205,206
27. 37.784 37.784 ↑ 7.7 6,668 1

Seq Scan on fact_count_4577 fc_13 (cost=0.00..4,117.31 rows=51,111 width=25) (actual time=0.110..37.784 rows=6,668 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 146,760
28. 88.613 88.613 ↑ 28.1 4,400 1

Seq Scan on fact_count_6030 fc_14 (cost=0.00..9,963.92 rows=123,713 width=25) (actual time=0.113..88.613 rows=4,400 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 366,764
29. 34.939 34.939 ↓ 0.0 0 1

Seq Scan on fact_count_6524 fc_15 (cost=0.00..3,956.71 rows=49,118 width=25) (actual time=34.939..34.939 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 147,344
30. 48.861 48.861 ↓ 0.0 0 1

Seq Scan on fact_count_7912 fc_16 (cost=0.00..5,531.63 rows=68,679 width=25) (actual time=48.861..48.861 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 206,036
31. 75.250 75.250 ↓ 0.0 0 1

Seq Scan on fact_count_8211 fc_17 (cost=0.00..8,460.85 rows=105,045 width=26) (actual time=75.250..75.250 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 315,134
32. 46.177 46.177 ↓ 0.0 0 1

Seq Scan on fact_count_8407 fc_18 (cost=0.00..5,223.96 rows=64,856 width=26) (actual time=46.177..46.177 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 194,606
33. 30.035 30.035 ↓ 0.0 0 1

Seq Scan on fact_count_8510 fc_19 (cost=0.00..3,403.28 rows=42,253 width=27) (actual time=30.035..30.035 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 126,748
34. 44.225 44.225 ↓ 0.0 0 1

Seq Scan on fact_count_8711 fc_20 (cost=0.00..4,997.49 rows=62,047 width=27) (actual time=44.225..44.225 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 186,072
35. 3.559 3.559 ↓ 0.0 0 1

Seq Scan on fact_count_8900 fc_21 (cost=0.00..399.02 rows=4,953 width=23) (actual time=3.559..3.559 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 14,870
36. 28.257 28.257 ↓ 0.0 0 1

Seq Scan on fact_count_9029 fc_22 (cost=0.00..3,129.17 rows=38,841 width=27) (actual time=28.257..28.257 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 116,620
37. 85.461 85.461 ↓ 0.0 0 1

Seq Scan on fact_count_9129 fc_23 (cost=0.00..9,362.92 rows=116,246 width=23) (actual time=85.461..85.461 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 348,812
38. 41.247 41.247 ↓ 0.0 0 1

Seq Scan on fact_count_9227 fc_24 (cost=0.00..4,664.56 rows=57,915 width=27) (actual time=41.247..41.247 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 173,676
39. 3.483 3.483 ↓ 0.0 0 1

Seq Scan on fact_count_9234 fc_25 (cost=0.00..389.30 rows=4,825 width=23) (actual time=3.483..3.483 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 14,486
40. 3.484 3.484 ↓ 0.0 0 1

Seq Scan on fact_count_9470 fc_26 (cost=0.00..387.49 rows=4,809 width=23) (actual time=3.484..3.484 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 14,440
41. 14.994 14.994 ↓ 0.0 0 1

Seq Scan on fact_count_189 fc_27 (cost=0.00..1,733.67 rows=20,775 width=32) (actual time=14.994..14.994 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 62,324
42. 2,924.897 2,924.897 ↑ 25,874.4 158 1

Seq Scan on fact_count_188 fc_28 (cost=0.00..341,068.42 rows=4,088,160 width=32) (actual time=1,364.165..2,924.897 rows=158 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 12,264,502
43. 1,445.006 1,445.006 ↓ 0.0 0 1

Seq Scan on fact_count_186 fc_29 (cost=0.00..167,861.08 rows=2,012,040 width=32) (actual time=1,445.006..1,445.006 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 6,036,176
44. 1,975.922 1,975.922 ↓ 0.0 0 1

Seq Scan on fact_count_187 fc_30 (cost=0.00..225,332.88 rows=2,700,912 width=32) (actual time=1,975.922..1,975.922 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 8,102,890
45. 19.420 19.420 ↓ 0.0 0 1

Seq Scan on fact_count_9917 fc_31 (cost=0.00..2,187.73 rows=27,157 width=27) (actual time=19.420..19.420 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 81,470
46. 24.503 24.503 ↓ 0.0 0 1

Seq Scan on fact_count_9842 fc_32 (cost=0.00..2,751.72 rows=34,166 width=27) (actual time=24.503..24.503 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 102,498
47. 9.742 9.742 ↓ 0.0 0 1

Seq Scan on fact_count_10009 fc_33 (cost=0.00..1,101.71 rows=13,671 width=27) (actual time=9.742..9.742 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 41,012
48. 23.737 23.737 ↓ 0.0 0 1

Seq Scan on fact_count_10109 fc_34 (cost=0.00..2,643.98 rows=32,819 width=27) (actual time=23.737..23.737 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 98,456
49. 25.219 25.219 ↓ 0.0 0 1

Seq Scan on fact_count_4461 fc_35 (cost=0.00..2,962.62 rows=34,888 width=36) (actual time=25.219..25.219 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 104,655
50. 427.034 427.034 ↓ 0.0 0 1

Seq Scan on fact_count_179 fc_36 (cost=0.00..49,718.83 rows=595,940 width=32) (actual time=427.034..427.034 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 1,787,822
51. 72.023 72.023 ↓ 0.0 0 1

Seq Scan on fact_count_9 fc_37 (cost=0.00..8,264.48 rows=99,438 width=30) (actual time=72.023..72.023 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 298,332
52. 14.507 14.507 ↓ 0.0 0 1

Seq Scan on fact_count_12117 fc_38 (cost=0.00..1,678.88 rows=20,112 width=32) (actual time=14.507..14.507 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 60,345
53. 2.082 2.082 ↓ 0.0 0 1

Seq Scan on fact_count_12856 fc_39 (cost=0.00..203.70 rows=2,432 width=32) (actual time=2.082..2.082 rows=0 loops=1)

  • Filter: ((date_trunc('hour'::text, timestamp_start))::date <= '2018-09-09'::date)
  • Rows Removed by Filter: 7,302
54. 0.118 0.247 ↑ 1.0 554 1

Hash (cost=26.54..26.54 rows=554 width=45) (actual time=0.247..0.247 rows=554 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
55. 0.129 0.129 ↑ 1.0 554 1

Seq Scan on dim_venue dv (cost=0.00..26.54 rows=554 width=45) (actual time=0.006..0.129 rows=554 loops=1)

56. 0.004 0.014 ↑ 1.0 2 1

Hash (cost=8.59..8.59 rows=2 width=4) (actual time=0.014..0.014 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 0.010 0.010 ↑ 1.0 2 1

Index Only Scan using dim_counter_pkey on dim_counter dc (cost=0.28..8.59 rows=2 width=4) (actual time=0.008..0.010 rows=2 loops=1)

  • Index Cond: (counter_id = ANY ('{10662,10694}'::integer[]))
  • Heap Fetches: 0
58. 0.000 0.000 ↓ 0.0 0

Sort (cost=281.29..290.51 rows=3,687 width=17) (never executed)

  • Sort Key: sh.country, sh.date
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on school_holiday sh (cost=0.00..62.87 rows=3,687 width=17) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Index Only Scan using public_holiday_date_country_state_idx on public_holiday ph (cost=0.29..0.34 rows=3 width=18) (never executed)

  • Index Cond: (date = ((date_trunc('hour'::text, fc.timestamp_start)))::date)
  • Heap Fetches: 0
Planning time : 4.235 ms
Execution time : 64,153.596 ms