API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2014
461
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.31 KB | None | 0 0
raw download clone embed print report
  1. -- Intel(R) Core(TM) i5-3570K CPU @ 3.40GHz
  2. -- 16 GB RAM
  3. -- SSD OCZ Vertex 2
  4. -- Linux black 3.12.8-031208-generic #201401151952 SMP Thu Jan 16 00:53:39 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
  5. -- Postgres 9.3.4
  6.  
  7. $ createdb aggtest
  8. $ psql aggtest
  9. psql (9.3.4)
  10. Type "help" for help.
  11.  
  12. aggtest=# create table randomdata (created_on timestamp without time zone, value double precision);
  13. CREATE TABLE
  14. aggtest=# copy randomdata from '/home/prook/Downloads/randomData.csv' with csv header;
  15. COPY 50000000
  16.  
  17. -- Import trval asi 2 minuty (od oka, nemereno)
  18.  
  19. -- Test A: 33.55 s
  20.  
  21. aggtest=# explain analyze verbose select extract(year from created_on), extract(doy from created_on), count(*), avg(value), min(value), max(value) from randomdata group by extract(year from created_on), extract(doy from created_on) order by extract(year from created_on), extract(doy from created_on);
  22. QUERY PLAN
  23. -----------------------------------------------------------------------------------------------------------------------------------------------
  24. Sort (cost=1705421.15..1705421.65 rows=200 width=16) (actual time=33555.000..33555.013 rows=367 loops=1)
  25. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (count(*)), (avg(value)), (min(value)), (max(value))
  26. Sort Key: (date_part('year'::text, randomdata.created_on)), (date_part('doy'::text, randomdata.created_on))
  27. Sort Method: quicksort Memory: 53kB
  28. -> HashAggregate (cost=1705410.01..1705413.51 rows=200 width=16) (actual time=33554.760..33554.805 rows=367 loops=1)
  29. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), count(*), avg(value), min(value), max(value)
  30. -> Seq Scan on public.randomdata (cost=0.00..987840.50 rows=47837967 width=16) (actual time=0.045..17491.283 rows=50000000 loops=1)
  31. Output: date_part('year'::text, created_on), date_part('doy'::text, created_on), value
  32. Total runtime: 33555.169 ms
  33. (9 rows)
  34.  
  35. -- Test B (bez indexu): 4.5 s
  36.  
  37. aggtest=# explain analyze verbose select extract(year from created_on), extract(doy from created_on), extract(hour from created_on), count(*), avg(value), min(value), max(value) from randomdata where created_on between '2012-07-16 00:00:00' and '2012-07-16 01:00:00' group by extract(year from created_on), extract(doy from created_on), extract(hour from created_on) order by extract(year from created_on), extract(doy from created_on), extract(hour from created_on);
  38. QUERY PLAN
  39. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  40. Sort (cost=1026521.03..1026521.03 rows=1 width=16) (actual time=4545.137..4545.138 rows=2 loops=1)
  41. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), (count(*)), (avg(value)), (min(value)), (max(value))
  42. Sort Key: (date_part('year'::text, randomdata.created_on)), (date_part('doy'::text, randomdata.created_on)), (date_part('hour'::text, randomdata.created_on))
  43. Sort Method: quicksort Memory: 25kB
  44. -> HashAggregate (cost=1026521.00..1026521.02 rows=1 width=16) (actual time=4545.128..4545.128 rows=2 loops=1)
  45. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), count(*), avg(value), min(value), max(value)
  46. -> Seq Scan on public.randomdata (cost=0.00..1022146.00 rows=250000 width=16) (actual time=3.485..4541.415 rows=5662 loops=1)
  47. Output: date_part('year'::text, created_on), date_part('doy'::text, created_on), date_part('hour'::text, created_on), value
  48. Filter: ((randomdata.created_on >= '2012-07-16 00:00:00'::timestamp without time zone) AND (randomdata.created_on <= '2012-07-16 01:00:00'::timestamp without time zone))
  49. Rows Removed by Filter: 49994338
  50. Total runtime: 4545.198 ms
  51. (11 rows)
  52.  
  53. -- Test B (s indexem na created_on, prvni spusteni): 0.06 s
  54.  
  55. aggtest=# create index created_on_idx on randomdata (created_on);
  56. CREATE INDEX
  57. aggtest=# explain analyze verbose select extract(year from created_on), extract(doy from created_on), extract(hour from created_on), count(*), avg(value), min(value), max(value) from randomdata where created_on between '2012-07-16 00:00:00' and '2012-07-16 01:00:00' group by extract(year from created_on), extract(doy from created_on), extract(hour from created_on) order by extract(year from created_on), extract(doy from created_on), extract(hour from created_on);
  58. QUERY PLAN
  59. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  60. Sort (cost=291230.09..291230.09 rows=1 width=16) (actual time=59.960..59.961 rows=2 loops=1)
  61. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), (count(*)), (avg(value)), (min(value)), (max(value))
  62. Sort Key: (date_part('year'::text, randomdata.created_on)), (date_part('doy'::text, randomdata.created_on)), (date_part('hour'::text, randomdata.created_on))
  63. Sort Method: quicksort Memory: 25kB
  64. -> HashAggregate (cost=291230.06..291230.08 rows=1 width=16) (actual time=59.950..59.950 rows=2 loops=1)
  65. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), count(*), avg(value), min(value), max(value)
  66. -> Bitmap Heap Scan on public.randomdata (cost=5307.06..286855.06 rows=250000 width=16) (actual time=2.731..54.738 rows=5662 loops=1)
  67. Output: date_part('year'::text, created_on), date_part('doy'::text, created_on), date_part('hour'::text, created_on), value
  68. Recheck Cond: ((randomdata.created_on >= '2012-07-16 00:00:00'::timestamp without time zone) AND (randomdata.created_on <= '2012-07-16 01:00:00'::timestamp without time zone))
  69. -> Bitmap Index Scan on created_on_idx (cost=0.00..5244.56 rows=250000 width=0) (actual time=1.415..1.415 rows=5662 loops=1)
  70. Index Cond: ((randomdata.created_on >= '2012-07-16 00:00:00'::timestamp without time zone) AND (randomdata.created_on <= '2012-07-16 01:00:00'::timestamp without time zone))
  71. Total runtime: 60.044 ms
  72. (12 rows)
  73.  
  74. -- Test B (s indexem na created_on, dalsi spusteni): 0.015 s
  75.  
  76. aggtest=# explain analyze verbose select extract(year from created_on), extract(doy from created_on), extract(hour from created_on), count(*), avg(value), min(value), max(value) from randomdata where created_on between '2012-07-16 00:00:00' and '2012-07-16 01:00:00' group by extract(year from created_on), extract(doy from created_on), extract(hour from created_on) order by extract(year from created_on), extract(doy from created_on), extract(hour from created_on);
  77. QUERY PLAN
  78. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  79. Sort (cost=291230.09..291230.09 rows=1 width=16) (actual time=15.922..15.923 rows=2 loops=1)
  80. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), (count(*)), (avg(value)), (min(value)), (max(value))
  81. Sort Key: (date_part('year'::text, randomdata.created_on)), (date_part('doy'::text, randomdata.created_on)), (date_part('hour'::text, randomdata.created_on))
  82. Sort Method: quicksort Memory: 25kB
  83. -> HashAggregate (cost=291230.06..291230.08 rows=1 width=16) (actual time=15.913..15.915 rows=2 loops=1)
  84. Output: (date_part('year'::text, created_on)), (date_part('doy'::text, created_on)), (date_part('hour'::text, created_on)), count(*), avg(value), min(value), max(value)
  85. -> Bitmap Heap Scan on public.randomdata (cost=5307.06..286855.06 rows=250000 width=16) (actual time=2.395..12.231 rows=5662 loops=1)
  86. Output: date_part('year'::text, created_on), date_part('doy'::text, created_on), date_part('hour'::text, created_on), value
  87. Recheck Cond: ((randomdata.created_on >= '2012-07-16 00:00:00'::timestamp without time zone) AND (randomdata.created_on <= '2012-07-16 01:00:00'::timestamp without time zone))
  88. -> Bitmap Index Scan on created_on_idx (cost=0.00..5244.56 rows=250000 width=0) (actual time=1.207..1.207 rows=5662 loops=1)
  89. Index Cond: ((randomdata.created_on >= '2012-07-16 00:00:00'::timestamp without time zone) AND (randomdata.created_on <= '2012-07-16 01:00:00'::timestamp without time zone))
  90. Total runtime: 15.992 ms
  91. (12 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!