-
Notifications
You must be signed in to change notification settings - Fork 22
/
vops.html
1039 lines (946 loc) · 53.8 KB
/
vops.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<html>
<head>
<title>Vectorized Operations (VOPS)</title>
<h1>Vectorized Operations (VOPS)</h1>
<ul>
<li><a href="#motivation">Motivation</a></li>
<li><a href="#profiling">Where DBMS spent most of the time during processing OLAP queries?</a></li>
<li><a href="#vertical">Vertical storage</a></li>
<li><a href="#vops">VOPS</a></li>
<ul>
<li><a href="#overview">Overview</a></li>
<li><a href="#types">Types</a></li>
<li><a href="#operators">Vector operators</a></li>
<li><a href="#aggregates">Vector aggregates</a></li>
<li><a href="#window">Vector window functions</a></li>
<li><a href="#indexes">Using indexes</a></li>
<li><a href="#populating">Preparing data for VOPS</a></li>
<li><a href="#vops_unnest">Back to normal tuples</a></li>
<li><a href="#fdw">Back to normal tables</a></li>
</ul>
<li><a href="#transform">Standard SQL query transformation</a></li>
<li><a href="#projections">Table projections</a></li>
<li><a href="#example">Example</a></li>
<li><a href="#performance">Performance evaluation</a></li>
<li><a href="#conclusion">Conclusion</a></li>
</ul>
</head>
<body>
<h2><a name="motivation">Motivation</a></h2>
<p>
PostgreSQL looks very competitive with other mainstream databases on OLTP workload (execution of large number of simple queries).
But on OLAP queries, requiring processing of larger volumes of data, DBMS-es oriented on analytic queries processing can provide an order of magnitude better speed.
Let's investigate why it happen and can we do something to make PostgreSQL efficient also for OLAP queries.
</p>
<h2><a name="profiling">Where DBMS spent most of the time during processing OLAP queries?</a></h2>
<p>
Profiling execution of queries shows several main factors, limiting Postgres performance:
</p>
<ol>
<li>
Unpacking tuple overhead (tuple_deform). To be able to access column values, Postgres needs to deform the tuple. Values can be compressed,
stored at some other page (TOAST), ... Also, as far as size of column can be varying, to extract N-th column we need to unpack preceding N-1 columns.
So deforming tuple is quite expensive operation, especially for tables with large number of attributes. In some cases rearranging columns in the table
allows to significantly reduce query execution time. Another universal approach is to split table into two: one small with frequently accessed scalar columns
and another with rarely used large columns. Certainly in this case we need to perform extra join but it allows to several times reduce
amount of fetched data. In queries like TPC-H Q6, tuple deform takes about 40% of total query execution time.
</li>
<li>
Interpretation overhead. Postgres compiler and optimizer build tree representing query execution plan. So query executor performs recursive invocation of
evaluate functions for nodes of this tree. Implementation of some nodes also contain switches used to select requested action.
So query plan is interpreted by Postgres query executor rather than directly executed. Usually interpreter is about 10 times slower than native code.
This is why elimination of interpretation overhead allows to several times increase query speed, especially for queries with complex predicates where most time is spent in
expression evaluation.
</li>
<li>
Abstraction penalty. Support of abstract (user defined) types and operations is one of the key features of Postgres. It's executor is able to deal not
only with built-in set of scalar types (like integer, real, ...) but with any types defined by user (for example complex, point,...).
But the price of such flexibility is that each operations requires function call. Instead of adding to integers directly, Postgres executor invokes
function which performs addition of two integers. Certainly in this case function call overhead is much larger then performed operation itself.
Function call overhead is also increased because of Postgres function call convention requiring passing parameter values through memory (not using register call convention).
</li>
<li>
Pull model overhead. Postgres executor is implementing classical Volcano-style query execution model - pull model. Operand's values are pulled by operator.
It simplifies executor and operators implementation. But it has negative impact on performance, because leave nodes (fetching tuple from heap or index page)
have to do a lot of extra work saving and restoring their context.
</li>
<li>
MVCC overhead. Postgres provides multiversion concurrency control, which allows multiple transactions to concurrently work with the same record without blocking each other. It is goods for frequently updated data (OLTP), but for read-only or append-only data in OLAP scenarios it adds just extra overhead.
Both space overhead (about 20 extra bytes per tuple) and CPU overhead (checking visibility of each tuple).
</li>
</ol>
<p>
There are many different ways of addressing this issues. For example we can use JIT (Just-In-Time) compiler to generate native code for query
and eliminate interpretation overhead and increase heap deform speed. We can rewrite optimizer from pull to push model.
We can try to optimize tuple format to make heap deforming more efficient. Or we can generate byte code for query execution plan which interpretation
is more efficient than recursive invocation of evaluate function for each node because of better access locality.
But all this approaches require significant rewriting of Postgres executor and some of them also require changes of all Postgres architecture.
</p>
<p>
But there is an approach which allows to address most of this issues without radical changes of executor. It is vector operations.
It is explained in next section.
</p>
<h2><a name="vertical">Vertical storage</a></h2>
<p>
Traditional query executor (like Postgres executor) deals with single row of data at each moment of time.
If it has to evaluate expression (x+y) then
it fetches value of "x", then value of "y", performs operation "+" and returns the result value to the upper node. In contrast
vectorized executor is able to process in one operation multiple values. In this case "x" and "y" represent not just a single scalar value,
but vector of values and result is also a vector of values. In vector execution model interpretation and function call overhead is divided by size of vector. The price of performing function call is the same, but as far as function proceeds N values instead of just one, this overhead become less critical.
</p>
<p>
What is the optimal size for the vector? From the explanation above it is clear that the larger vector is, the less per-row overhead we have.
So we can form vector from all values of the correspondent table attribute. It is so called vertical data model or columnar store.
Unlike classical "horizontal" data model where the unit of storing data is row (tuple), here we have vertical columns.
Columnar store has the following main advantages:
</p>
<ul>
<li>Reduce size of fetched data: only columns used in query need to be fetched</li>
<li>Better compression: storing all values of the same attribute together makes it possible to much better and faster compress them,
for example using delta encoding.</li>
<li>Minimize interpretation overhead: each operation is perform not for single value, but for set of values</li>
<li>Use CPU vector instruction (SIMD) to process data</li>
</ul>
<p>
There are several DBMS-es implementing columnar store model. Most popular are <a href="https://vertica.com/">Vertical</a>,
<a href="https://www.monetdb.org/Home">MonetDB</a>. But actually performing operation on the whole column is not so good idea.
Table can be very large (OLAP queries are used to work with large data sets), so vector can also be very big and even doesn't fit in memory.
But even if it fits in memory, working with such larger vectors prevent efficient utilization of CPU caches (L1, L2,...).
Consider expression (x+y)*(x-y). Vector executor performs addition of two vectors : "x" and "y" and produces result vector "r1".
But when last element of vector "r" is produced, first elements of vector "r1" are already thrown from CPU cache, as well as first elements of "x" and "y" vectors.
So when we need to calculate (x-y) we once again have to load data for "x" and "y" from slow memory to fast cache.
Then we produce "r2" and perform multiplication of "r1" and "r2". But here we also need first to load data for this vectors into the CPU cache.
</p>
<p>
So it is more efficient to split column into relatively small <i>chunks</i> (or <i>tiles</i> - there is no single notion for it accepted by everyone).
This chunk is a unit of processing by vectorized executor.
Size of such chunk is chosen to keep all operands of vector operations in cache even for complex expressions.
Typical size of chunk is from 100 to 1000 elements.
So in case of (x+y)*(x-y) expression, we calculate it not for the whole column but only for 100 values (assume that size of the chunk is 100).
Splitting columns into chunks in successors of MonetDB x100 and HyPer allows to increase speed up to ten times.
</p>
<h2><a name="vops">VOPS</a></h2>
<h3><a name="overview">Overview</a></h2>
<p>
There are several attempts to integrate columnar store in PostgreSQL.
The most known is <a href="https://github.com/citusdata/cstore_fdw">CStore FDW</a> by CitusDB. It is implemented as foreign data wrapper (FDW)
and is efficient for queries fetching relatively small fraction of columns. But it is using standard Postgres raw-based executor and so is not able
to take advantages of vector processing. There is interesting <a href="https://github.com/citusdata/postgres_vectorization_test">project</a> done by CitusDB
intern. He implements vector operations on top of CStore using executors hooks for some nodes. IT reports 4-6 times speedup for grand aggregates and 3 times
speedup for aggregation with group by.
</p>
<p>
Another project is <a href="https://github.com/knizhnik/imcs">IMCS</a>: In-Memory Columnar Store. Here columnar store is implemented in memory and is accessed
using special functions. So you can not use standard SQL query to work with this storage - you have to rewrite it using IMCS functions.
IMCS provides vector operations (using tiles) and parallel execution.
</p>
<p>
Both CStore and IMCS are keeping data outside Postgres. But what if we want to use vector operations for data kept in standard Postgres tables?
Definitely, the best approach is to impalement alternative heap format. Or even further: eliminate notion of heap at all - treat heap just as yet another
access method, similar with other indexes.
</p>
<p>
But such radical changes requires deep redesign of all Postgres architecture. It will be better to estimate first possible advantages we can expect from usage of vector vector operations. Vector executor is widely discussed in Postgres forums, but efficient vector executor is not possible without
underlying support at storage layer. Advantages of vector processing will be annihilated if vectors are formed from attributes of rows extracted from existed Postgres heap page.
</p>
<p>
The idea of VOPS extension is to implement vector operations for tiles represented as special Postgres types. Tiles should be used as table column types instead of scalar types. For example instead of "real" we should use "vops_float4" which is tile representing up to 64 values of the correspondent column.
Why 64? There are several reasons for choosing this number:
</p>
<ol>
<li>We provide efficient access to tiles, we need that
size_of_tile*size_of_attribute*number_of_attributes is smaller than page size. Typical record contains about 10 attributes, default size of Postgres page is 8kb.
</li>
<li>64 is number of bits in large word. We need to maintain bitmask to mark null values. Certainly it is possible to store bitmask in array with arbitrary size, but manipulation with single 64-bit integer is more efficient.</li>
</li>
<li>Due to the arguments above, to efficiently utilize cache, size of tile should be in range 100..1000.
</li>
</ol>
<p>
VOPS is implemented as Postgres extension. It doesn't change anything in Postgres executor and page format.
It also doesn't setup any executors hooks or alter query execution plan. The main idea of this project was to measure speedup which
can be reached by using vector operation with existed executor and heap manager. VOPS provides set of standard operators for
tile types, allowing to write SQL queries in the way similar with normal SQL queries. Right now vector operators can be used
inside predicates and aggregate expressions. Joins are not currently supported.
Details of VOPS architecture are described below.
</p>
<h3><a name="types">Types</a></h3>
<p>
VOPS supports all basic Postgres numeric types: 1,2,4,8 byte integers and 4,8 bytes floats.
Also it supports <code>date</code> and <code>timestamp</code> types but them are using the same implementation as
<code>int4</code> and <code>int8</code> correspondingly.
</p>
<table border>
<tr><th>SQL type</th><th>C type</th><th>VOPS tile type</th></tr>
<tr><td>bool</td><td>bool</td><td>vops_bool</td></tr>
<tr><td>"char"</td><td>char</td><td>vops_char</td></tr>
<tr><td>int2</td><td>int16</td><td>vops_int2</td></tr>
<tr><td>int4</td><td>int32</td><td>vops_int4</td></tr>
<tr><td>int8</td><td>int64</td><td>vops_int8</td></tr>
<tr><td>float4</td><td>float4</td><td>vops_float4</td></tr>
<tr><td>float8</td><td>float8</td><td>vops_float8</td></tr>
<tr><td>date</td><td>DateADT</td><td>vops_date</td></tr>
<tr><td>timestamp</td><td>Timestamp</td><td>vops_timestamp</td></tr>
<tr><td>interval</td><td>Interval</td><td>vops_interval</td></tr>
<tr><td>char(n)</td><td>text</td><td>vops_text(n)</td></tr>
<tr><td>varchar(n)</td><td>text</td><td>vops_text(n)</td></tr>
</table>
<h3><a name="operators">Vector operators</a></h3>
<p>
VOPS provides implementation of all built-in SQL arithmetic operations for
numeric types: <b>+ - / *</b>
Certainly it also implements all comparison operators: <b>= <> > >= < <=</b>.
Operands of such operators can be either tiles, either scalar constants: <code>x=y</code> or <code>x=1</code>.
</p>
<p>
Boolean operators <code>and</code>, <code>or</code>, <code>not</code> can not be overloaded.
This is why VOPS provides instead of them operators <b>& | !</b>. Please notice that precedence of this operators is different
from <code>and</code>, <code>or</code>, <code>not</code> operators. So you can not write predicate as <code>x=1 | x=2</code> - it will
cause syntax error. To solve this problem please use parenthesis: <code>(x=1) | (x=2)</code>.
</p>
<p>
Also VOPS provides analog of between operator. In SQL expression <code>(x BETWEEN a AND b)</code> is equivalent to
<code>(x >= a AND x <= b)</code>. But as far as AND operator can not be overloaded, such substitution will not work for VOPS tiles.
This is why VOPS provides special function for range check. Unfortunately <code>BETWEEN</code> is reserved keyword, so
no function with such name can be defined. This is why synonym <code>BETWIXT</code> is used.
</p>.
<p>
Postgres requires predicate expression to have boolean type. But result of vector boolean operators is <code>vops_bool</code>, not <code>bool</code>.
This is why compiler doesn't allow to use it in predicate. The problem can be solved by introducing special <code>filter</code> function.
This function is given arbitrary vector boolean expression and returns normal boolean which ... is always true.
So from Postgres executor point of view predicate value is always true. But <code>filter</code> function sets <code>filter_mask</code>
which is actually used in subsequent operators to determine selected records.
So query in VOPS looks something like this:
</p>
<pre>
select sum(price) from trades where filter(day >= '2017-01-01'::date);
</pre>
<p>
Please notice one more difference from normal sequence: we have to use explicit cast of string constant to appreciate data type (<code>date</code> type in this example). For <code>betwixt</code> function it is not needed:
</p>
<pre>
select sum(price) from trades where filter(betwixt(day, '2017-01-01', '2017-02-01'));
</pre>
<p>
For <code>char</code>, <code>int2</code> and <code>int4</code> types VOPS provides concatenation operator <b>||</b> which produces doubled integer type:
<code>(char || char) -> int2</code>, <code>(int2 || int2) -> int4</code>, <code>(int4 || int4) -> int8</code>.
Them can be used for grouping by several columns (see below).
</p>
<table border>
<tr><th>Operator</th><th>Description</th></tr>
<tr><td><code>+</code></td><td>Addition</td></tr>
<tr><td><code>-</code></td><td>Binary subtraction or unary negation</td></tr>
<tr><td><code>*</code></td><td>Multiplication</td></tr>
<tr><td><code>/</code></td><td>Division</td></tr>
<tr><td><code>=</code></td><td>Equals</td></tr>
<tr><td><code><></code></td><td>Not equals</td></tr>
<tr><td><code><</code></td><td>Less than</td></tr>
<tr><td><code><=</code></td><td>Less than or Equals</td></tr>
<tr><td><code>></code></td><td>Greater than</td></tr>
<tr><td><code>>=</code></td><td>Greater than or equals</td></tr>
<tr><td><code>&</code></td><td>Boolean AND</td></tr>
<tr><td><code>|</code></td><td>Boolean OR</td></tr>
<tr><td><code>!</code></td><td>Boolean NOT</td></tr>
<tr><td><code>bitwixt(x,low,high)</code></td><td>Analog of BETWEEN</td></tr>
<tr><td><code>is_null(x)</code></td><td>Analog of IS NULL</td></tr>
<tr><td><code>is_not_null(x)</code></td><td>Analog of IS NOT NULL</td></tr>
<tr><td><code>ifnull(x,subst)</code></td><td>Analog of COALESCE</td></tr>
</table>
<h3><a name="aggregates">Vector aggregates</a></h3>
<p>
OLAP queries usually perform some kind of aggregation of large volumes of data. These includes <code>grand</code> aggregates which are calculated for the whole
table or aggregates with <code>group by</code> which are calculated for each group.
VOPS implements all standard SQL aggregates: <code>count, min, max, sum, avg, var_pop, var_sampl, variance, stddev_pop, stddev_samp, stddev</code>. Also it provides <code>approaxdc</code> for approximation of distinct count. Them can be used exactly in the same way as in normal SQL queries:
</p>
<pre>
select sum(l_extendedprice*l_discount) as revenue
from vops_lineitem
where filter(betwixt(l_shipdate, '1996-01-01', '1997-01-01')
& betwixt(l_discount, 0.08, 0.1)
& (l_quantity < 24));
</pre>
<p>
Also VOPS provides weighted average aggregate VWAP which can be used to calculate volume-weighted average price:
</p>
<pre>
select wavg(l_extendedprice,l_quantity) from vops_lineitem;
</pre>
<p>
It is possible to get first/last value of each group using first/last aggregates.
This aggregates require two vector arguments: first one is value itself and second one - is timestamp by which sorting in each group is done:
</p>
<pre>
select first(bid_price,ts),last(ask_price,ts) from vquote group by symbol;
</pre>
<p>
Using aggregation with group by is more complex. VOPS provides two functions for it: <code>map</code> and <code>reduce</code>.
The work is actually done by <b>map</b>(<i>group_by_expression</i>, <i>aggregate_list</i>, <i>expr</i> {, <i>expr</i> })
VOPS implements aggregation using hash table, which entries collect aggregate states for all groups. And set returning function <code>reduce</code> just
iterates through the hash table consrtucted by <code>map</code>. <code>reduce</code> function is needed because result of aggregate in Postgres can not be a set. So aggregate query with group by looks something like this:
</p>
<pre>
select reduce(map(l_returnflag||l_linestatus, 'sum,sum,sum,sum,avg,avg,avg',
l_quantity,
l_extendedprice,
l_extendedprice*(1-l_discount),
l_extendedprice*(1-l_discount)*(1+l_tax),
l_quantity,
l_extendedprice,
l_discount)) from vops_lineitem where filter(l_shipdate <= '1998-12-01'::date);
</pre>
<p>
Here we use concatenation operator to perform grouping by two columns.
Right now VOPS supports grouping only by integer type.
Another serious restriction is that all aggregated expressions should have the same type, for example <code>vops_float4</code>.
It is not possible to calculate aggregates for <code>vops_float4</code> and <code>vopd_int8</code> columns in one call of <code>map</code> function,
because it accepts aggregation arguments as variadic array, so all elements of this array should have the same type.
</p>
<p>
Aggregate string in <code>map</code> function should contain list of requested aggregate functions, separated by colon.
Standard lowercase names should be used: <code>count, sum, agg, min, max</code>. Count is executed for the particular column: <code>count(x)</code>.
There is no need to explicitly specify <code>count(*)</code> because number of records in each group is returned by <code>reduce</code> function in any case.
</p>
<p>
<code>reduce</code> function returns set of <code>vops_aggregate</code> type.
It contains three components: value of group by expression, number of records in the group and array of floats with aggregate values.
Please notice that values of all aggregates, including <code>count</code> and <code>min/max</code>, are returned as floats.
</p>
<pre>
create type vops_aggregates as(group_by int8, count int8, aggs float8[]);
create function reduce(bigint) returns setof vops_aggregates;
</pre>
<p>
But there is much simple and straightforward way of performing group aggregates using VOPS.
We need to partition table by <i>group by</i> fields. In this case grouping keys will be stored in normal way and other fields - inside tiles.
Now Postgres executor will execute VOPS aggregates for each group:
</p>
<pre>
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
countall(*) as count_order
from
vops_lineitem_projection
where
filter(l_shipdate <= '1998-12-01'::date)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
</pre>
<p>
In this example <code>l_returnflag</code> and <code>l_linestatus</code> fields of table vops_lineitem_projection have <code>"char"</code> type
while all other used fields - tile types (<code>l_shipdate</code> has type <code>vops_date</code> and other fields - <code>vops_float4</code>).
The query above is executed even faster than query with <code>reduce(map(...))</code>.
The main problem with this approach is that you have to create projection for each combination of group by keys you want to use in queries.
</p>
<h3><a name="window">Vector window functions</a></h3>
<p>
VOPS provides limited support of Postgres window functions. It implements <code>count, sum, min, max, avg</code> and <code>lag</code> functions.
But unfortunately Postgres requires aggregates to have to similar final type for moving (window) and plain implementations.
This is why VOPS has to choose define this aggregate under different names: <code>mcount, msum, mmin, mmax, mavg</code>.
</p>
<p>
There are also two important restrictions:
</p>
<ol>
<li>Filtering, grouping and sorting can be done only by scalar (non-tile) attributes</li>
<li>Only <code>rows between unbounded preceding and current row</code> frame is supported (but there is special version of <code>msum</code> which accepts extra window size parameter)</li>
</ol>
<p>
Example of using window functions with VOPS:
</p>
<pre>
select vops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w
from v window w as (rows between unbounded preceding and current row)) t;
</pre>
<h3><a name="indexes">Using indexes</a></h3>
<p>
Analytic queries are usually performed on the data for which no indexes are defined.
And columnar store vector operations are most efficient in this case.
But it is still possible to use indexes with VOPS.
</p>
<p>
As far as each VOPS tile represents multiple values, index can be used only for some preliminary, non-precise filtering of data.
It is something similar with BRIN indexes.
VOPS provides four functions: <code>first, last, high, low</code> which can be used to obtain high/low boundary of values stored in the tile.
First two functions <code>first</code> and <code>last</code> should be used for sorted data set. In this case first value is the smallest value in the tile
and last value is the largest value in the tile. If data is not sorted, then <code>low</code and <code>high</code> functions should be used, which
are more expensive, because them need to inspect all tile values.
Using this four function it is possible to construct functional indexes for VOPS table.
BRIN index seems to be the best choice for VOPS table:
</p>
<pre>
create index low_boundary on trades using brin(first(day)); -- trades table is ordered by day
create index high_boundary on trades using brin(last(day)); -- trades table is ordered by day
</pre>
<p>
Now it is possible to use this indexes in query. Please notice that we have to recheck precise condition
because index gives only approximate result:
</p>
<pre>
select sum(price) from trades where first(day) >= '2015-01-01' and last(day) <= '2016-01-01'
and filter(betwixt(day, '2015-01-01', '2016-01-01'));
</pre>
<h3><a name="populating">Preparing data for VOPS</a></h3>
<p>
Now the most interesting question (from which may be we should start) - how we managed to prepare data for VOPS queries?
Who and how will combine attribute values of several rows inside one VOPS tile?
It is done by <code>populate</code> functions, provided by VOPS extension.
</p>
<p>
First of all you need to create table with columns having VOPS tile types.
It can map all columns of the original table or just some most frequently used subset of them.
This table can be treated as <code>projection</code> of original table
(this concept of projections is taken from Vertica).
Projection should include columns which are most frequently used together in queries.
</p>
<p>
Original table from TPC-H benchmark:
</p>
<pre>
create table lineitem(
l_orderkey integer,
l_partkey integer,
l_suppkey integer,
l_linenumber integer,
l_quantity real,
l_extendedprice real,
l_discount real,
l_tax real,
l_returnflag "char",
l_linestatus "char",
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment char(44));
</pre>
<p>
VOPS projection of this table:
</p>
<pre>
create table vops_lineitem(
l_shipdate vops_date not null,
l_quantity vops_float4 not null,
l_extendedprice vops_float4 not null,
l_discount vops_float4 not null,
l_tax vops_float4 not null,
l_returnflag vops_char not null,
l_linestatus vops_char not null
);
</pre>
<p>
Original table can be treated as write optimized storage (WOS).
If it has not indexes, then Postgres is able to provide very fast insertion speed, comparable with raw disk write speed.
Projection in VOPS format can be treated as read-optimized storage (ROS), most efficient for execution of OLAP queries.
</p>
<p>
Data can be transferred from original to projected table using VOPS <code>populate</code> function:
</p>
<pre>
create function populate(destination regclass,
source regclass,
predicate cstring default null,
sort cstring default null) returns bigint;
</pre>
<p>
Two first mandatory arguments of this function specify target and source tables.
Optional predicate and sort clauses allow to restrict amount of imported data and enforce requested order.
By specifying predicate it is possible to update VOPS table using only most recently received records.
This functions returns number of loaded records.
Example of populate function invocation:
</p>
<pre>
select populate(destination := 'vops_lineitem'::regclass, source := 'lineitem'::regclass);
</pre>
<p>
You can use populated table in queries performing sequential scan. VOPS operators can speed up filtering of records
and calculation of aggregates. Aggregation with <code>group by</code> requires use of <code>reduce + map</code> functions.
But as it was mentioned above in the section describing aggregates, it is possible to populate table in such way,
that standard Postgres grouping algorithm will be used.
</p>
<p>
We need to choose partitioning keys and sort original table by this keys.
Combination of partitioning keys expected to be NOT unique - otherwise tiles can only increase used space and lead to performance degradation.
But if there are a lot of duplicates, then "collapsing" them and storing other fields in tiles will help to reduce space and speed up queries.
Let's create the following projection of <code>lineitems</code> table:
</p>
<pre>
create table vops_lineitem_projection(
l_shipdate vops_date not null,
l_quantity vops_float4 not null,
l_extendedprice vops_float4 not null,
l_discount vops_float4 not null,
l_tax vops_float4 not null,
l_returnflag "char" not null,
l_linestatus "char" not null
);
</pre>
<p>
As you can see, in this table <code>l_returnflag</code> and <code>l_linestatus</code> fields are scalars, and other fields - tiles.
This projection can be populated using the following command:
</p>
<pre>
select populate(destination := 'vops_lineitem_projection'::regclass, source := 'lineitem_projection'::regclass, sort := 'l_returnflag,l_linestatus');
</pre>
<p>
Now we can create normal index on partitioning keys, define standard predicates for them and use them in <code>group by</code> and
<code>order by</code> clauses.
</p>
<p>
Sometimes it is not possible or not desirable to store two copies of the same dataset. VOPS allows to load data directly from CSV file into VOPS
table with tiles, bypassing creation of normal (plain) table. It can be done using <code>import</code> function:
</p>
<pre>
select import(destination := 'vops_lineitem'::regclass, csv_path := '/mnt/data/lineitem.csv', separator := '|');
</pre>
<p>
<code>import</code> function is defined in this way:
</p>
<pre>
create function import(destination regclass,
csv_path cstring,
separator cstring default ',',
skip integer default 0) returns bigint;
</pre>
<p>
It accepts name of target VOPS table, path to CSV file, optional separator (default is ',') and number of lines in CSV header
(no header by default). The function returns number of imported rows.
</p>
<h3><a name="vops_unnest">Back to normal tuples</a></h3>
<p>
A query from VOPS projection returns set of tiles. Output function of tile type is able to print content of the tile.
But in some cases it is preferable to transfer result to normal (horizontal) format where each tuple represents one record.
It can be done using <code>vops_unnest</code> function:
</p>
<pre>
postgres=# select vops_unnest(l.*) from vops_lineitem l where filter(l_shipdate <= '1998-12-01'::date) limit 3;
vops_unnest
---------------------------------------
(1996-03-13,17,33078.9,0.04,0.02,N,O)
(1996-04-12,36,38306.2,0.09,0.06,N,O)
(1996-01-29,8,15479.7,0.1,0.02,N,O)
(3 rows)
</pre>
<h3><a name="fdw">Back to normal tables</a></h3>
<p>
As it was mentioned in previous section, <code>vops_unnest</code> function can scatter records with VOPS types into normal records with scalar types.
So it is possible to use this records in arbitrary SQL queries.
But there are two problems with vops_unnest function:
</p>
<ol>
<li>It is not convenient to use. This function has no static knowledge about the format of output record and this is why programmer has to specify it manually,
if here wants to decompose this record.</li>
<li>PostgreSQL optimizer has completely no knowledge on result of transformation performed by vops_unnest() function.
This is why it is not able to choose optimal query execution plan for data retrieved from VOPS table.</li>
</ol>
<p>
Fortunately Postgres provides solution for both of this problem: foreign data wrappers (FDW). In our case data is not really "foreign": it is stored inside our own database.
But in alternatives (VOPS) format. VOPS FDW allows to "hide" specific of VOPS format and run normal SQL queries on VOPS tables.
FDW allows the following:
</p>
<ol>
<li>Extract data from VOPS table in normal (horizontal) format so that it can be proceeded by upper nodes in query execution plan.</li>
<li>Pushdown to VOPS operations that can be efficiently executed using vectorized operations on VOPS types: filtering and aggregation.</li>
<li>Provide statistic for underlying table which can be used by query optimizer.</li>
</ol>
<p>
So, by placing VOPS projection under FDW, we can efficiently perform sequential scan and aggregation queries as if them will be explicitly written for VOPS table
and at the same time be able to execute any other queries on this data, including joins, CTEs,...
Query can be written in standard SQL without usage of any VOPS specific functions.
</p>
<p>
Below is an example of creating VOPS FDW and running some queries on it:
</p>
<pre>
create foreign table lineitem_fdw (
l_suppkey int4 not null,
l_orderkey int4 not null,
l_partkey int4 not null,
l_shipdate date not null,
l_quantity float4 not null,
l_extendedprice float4 not null,
l_discount float4 not null,
l_tax float4 not null,
l_returnflag "char" not null,
l_linestatus "char" not null
) server vops_server options (table_name 'vops_lineitem');
explain select
sum(l_extendedprice*l_discount) as revenue
from
lineitem_fdw
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
QUERY PLAN
---------------------------------------------------------
Foreign Scan (cost=1903.26..1664020.23 rows=1 width=4)
(1 row)
-- Filter was pushed down to FDW
explain select
n_name,
count(*),
sum(l_extendedprice * (1-l_discount)) as revenue
from
customer_fdw join orders_fdw on c_custkey = o_custkey
join lineitem_fdw on l_orderkey = o_orderkey
join supplier_fdw on l_suppkey = s_suppkey
join nation on c_nationkey = n_nationkey
join region on n_regionkey = r_regionkey
where
c_nationkey = s_nationkey
and r_name = 'ASIA'
and o_orderdate >= '1996-01-01'
and o_orderdate < '1997-01-01'
group by
n_name
order by
revenue desc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2337312.28..2337312.78 rows=200 width=48)
Sort Key: (sum((lineitem_fdw.l_extendedprice * ('1'::double precision - lineitem_fdw.l_discount)))) DESC
-> GroupAggregate (cost=2336881.54..2337304.64 rows=200 width=48)
Group Key: nation.n_name
-> Sort (cost=2336881.54..2336951.73 rows=28073 width=40)
Sort Key: nation.n_name
-> Hash Join (cost=396050.65..2334807.39 rows=28073 width=40)
Hash Cond: ((orders_fdw.o_custkey = customer_fdw.c_custkey) AND (nation.n_nationkey = customer_fdw.c_nationkey))
-> Hash Join (cost=335084.53..2247223.46 rows=701672 width=52)
Hash Cond: (lineitem_fdw.l_orderkey = orders_fdw.o_orderkey)
-> Hash Join (cost=2887.07..1786058.18 rows=4607421 width=52)
Hash Cond: (lineitem_fdw.l_suppkey = supplier_fdw.s_suppkey)
-> Foreign Scan on lineitem_fdw (cost=0.00..1512151.52 rows=59986176 width=16)
-> Hash (cost=2790.80..2790.80 rows=7702 width=44)
-> Hash Join (cost=40.97..2790.80 rows=7702 width=44)
Hash Cond: (supplier_fdw.s_nationkey = nation.n_nationkey)
-> Foreign Scan on supplier_fdw (cost=0.00..2174.64 rows=100032 width=8)
-> Hash (cost=40.79..40.79 rows=15 width=36)
-> Hash Join (cost=20.05..40.79 rows=15 width=36)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on nation (cost=0.00..17.70 rows=770 width=40)
-> Hash (cost=20.00..20.00 rows=4 width=4)
-> Seq Scan on region (cost=0.00..20.00 rows=4 width=4)
Filter: ((r_name)::text = 'ASIA'::text)
-> Hash (cost=294718.76..294718.76 rows=2284376 width=8)
-> Foreign Scan on orders_fdw (cost=0.00..294718.76 rows=2284376 width=8)
-> Hash (cost=32605.64..32605.64 rows=1500032 width=8)
-> Foreign Scan on customer_fdw (cost=0.00..32605.64 rows=1500032 width=8)
-- filter on orders range is pushed to FDW
</pre>
<p>
Unfortunately Postgres FDW mechanism is not parallel safe: it is using cursors which right now doesn't support parallel execution.
It means that queries on foreign tables can ont use parallel plans, so them are so several times (proportional to number of parallel workers)
slower than queries on normal tables. This is why it is ont recommended to use VOPS in this way.
</p>
<h2><a name="transform">Standard SQL query transformation</a></h2>
<p>
Previous section describes VOPS specific types, operators, functions,...
Good news! You do not need to learn them. You can use normal SQL.
Well, it is still responsibility of programmer or database administrator to create proper projections
of original table. This projections need to use tiles types for some attributes (vops_float4,...).
Then you can query this table using standard SQL. And this query will be executed using vector operations!
</p>
<p>
How it works? There are absolutely no magic here. There are four main components of the puzzle:
</p>
<ol>
<li>User defined types</li>
<li>User defined operator</li>
<li>User defined implicit type casts</li>
<li>Post parse analyze hook which performs query transformation</li>
</ol>
<p>
So VOPS defines tile types and standard SQL operators for this types.
Then it defines implicit type cast from <code>vops_bool</code> (result of boolean operation with tiles)
to boolean type. Now programmer do not have to wrap vectorized boolean operations in <code>filter()</code>
function call. And the final transformation is done by post parse analyze hook, defined by VOPS extension.
It replaces scalar boolean operations with vector boolean operations:
</p>
<table border>
<tr><th>Original expression</th><th>Result of transformation</th></tr>
<tr><td><code>NOT filter(o1)</code></td><td><code>filter(vops_bool_not(o1))</code></td></tr>
<tr><td><code>filter(o1) AND filter(o2)</code></td><td><code>filter(vops_bool_and(o1, o2))</code></td></tr>
<tr><td><code>filter(o1) OR filter(o2)</code></td><td><code>filter(vops_bool_or(o1, o2))</code></td></tr>
</table>
<p>
Now there is no need to use VOPS specific <code>BETIXT</code> operator: standard SQL <code>BETWEEN</code> operator will work (but still using <code>BETIXT</code> is slightly more efficient, because it performs both comparions in one function). Also there are no problems with operators precedence and extra parenthesis are not needed. If query includes vectorized aggregates, then <code>count(*)</code> is transformed to <code>countall(*)</code>.
</p>
<p>
There is only one difference left between standard SQL and its vectorized extension. You still have to perform explicit type cast in case of using string literal, for example <code>l_shipdate <= '1998-12-01'</code>
will not work for <code>l_shipdate</code> column with tile type. Postgres have two overloaded versions
of <= operator which can be applied here:
</p>
<ol>
<li><code>vops_date</code> <b><=</b> <code>vops_date</code></li>
<li><code>vops_date</code> <b><=</b> <code>date</code></li>
</ol>
<p>
And it decides that it is better to convert string to the tile type <code>vops_date</code>.
In principle, it is possible to provide such conversion operator. But it is not good idea, because we have to
generate dummy tile with all components equal to the specified constant and perform
(<i>vector</i> <b>OP</b> <i>vector</i>) operation instead of more efficient (<i>vector</i> <b>OP</b> <i>scalar</i>).
</p>
<p>
There is one pitfall with post parse analyze hook: it is initialized in the extension <code>_PG_init</code> function.
But if extension was not registered in <code>shared_preload_libraries</code> list, then it will be loaded on demand when any function of this extension is requested.
Unfortunately it happens <b>after</b> parse analyze is done. So first time you execute VOPS query, it will not be transformed. You can get wrong result in this case.
Either take it in account, either add <code>vops</code> to <code>session_preload_libraries</code> configuration string.
VOPS extension provides special function <code>vops_initialize()</code> which can be invoked to force initialization of VOPS extension.
After invocation of this function, extension will be loaded and all subsequent queries will be normally transformed and produce expected results.
But more convenient way is to add 'vops' to <code>session_preload_libraries</code> parameter.
</p>
<h2><a name="projections">Table projections</a></h2>
<p>
In previous section we described how it is possible to write queries for VOPS tables using normal SQL.
This VOPS tables can be treated as projections of the main tables.
</p>
<p>
VOPS provides some functions simplifying creation and usage of projections.
In future it may be added to SQL grammar, so that it is possible to write
"CREATE PROJECTION xxx OF TABLE yyy(column1, column2,...) GROUP BY (column1, column2, ...)".
But right now it can be done using <code>create_projection(projection_name text, source_table regclass, vector_columns text[], scalar_columns text[] default null, order_by text default null)</code> function.
First argument of this function specifies name of the projection, second refers to existed Postgres table, <code>vector_columns</code> is array of
column names which should be stores as VOPS tiles, <code>scalar_columns</code> is array of grouping columns which type is preserved and
optional <code>order_by</code> parameter specifies name of ordering attribute (explained below).
The <code>create_projection(PNAME,...)</code> functions does the following:
</p>
<ol>
<li>Creates projection table with specified name and attributes.</li>
<li>Creates PNAME_refresh() functions which can be used to update projection.</li>
<li>Creates functional BRIN indexes for <code>first()</code> and <code>last()</code> functions of ordering attribute (if any)</li>
<li>Creates BRIN index on grouping attributes (if any)</li>
<li>Insert information about created projection in <code>vops_projections</code> table. This table is used by optimizer to
automatically substitute table with partition.</li>
</ol>
<p>
The <code>order_by</code> attribute is on of the VOPS projection vector columns by which data is sorted. Usually it is some kind of timestamp
used in <i>time series</i> (for example trade date). Presence of such column in projection allows to incrementally update projection.
Generated <code>PNAME_refresh()</code> method calls <code>populate</code> method with correspondent values of <code>predicate</code> and
<code>sort</code> parameters, selecting from original table only rows with <code>order_by</code> column value greater than maximal
value of this column in projection. It assumes that <code>order_by</code> is unique or at least refresh is done at the moment when there is some gap
in collected events. In addition to <code>order_by</code>, sort list for <code>populate</code> includes all scalar (grouping) columns.
It allows to efficiently group imported data by scalar columns and fill VOPS tiles (vector columns) with data.
</p>
<p>
When <code>order_by</code> attribute is specified, VOPS creates two functional BRIN indexes on <code>first()</code> and <code>last()</code>
functions of this attribute. Presence of such indexes allows to efficiently select time slices. If original query contains
predicates like <code>(trade_date between '01-01-2017' and '01-01-2018')</code> then VOPS projection substitution mechanism adds
<code>(first(trade_date) >= '01-01-2017' and last(trade_date) >= '01-01-2018')</code> conjuncts which allows Postgres optimizer to use BRIN
indexes to locate affected pages.
</p>
<p>
In in addition to BRIN indexes for <code>order_by</code> attribute, VOPS also creates BRIN index for grouping (scalar) columns.
Such index allows to efficiently select groups and perform index join.
</p>
<p>
Like materialized views, VOPS projections are not updated automatically. It is responsibility of programmer to periodically refresh them.
Certainly it is possible to define trigger or rule which will automatically insert data in projection table when original table is updated.
But such approach will be extremely inefficient and slow. To take advantage of vector processing, VOPS has to group data in tiles.
It can be done only if there is some batch of data which can be grouped by scalar attributes. If you insert records in projection table on-by-one,
then most of VOPS tiles will contain just one element.
The most convenient way is to use generated <code>PNAME_refresh()</code> function.
If <code>order_by</code> attribute is specified, this function imports from original table only the new data (not present in projection).
</p>
<p>
The main advantage of VOPS projection mechanism is that it allows to automatically substitute queries on original tables with projections.
There is <code>vops.auto_substitute_projections</code> configuration parameter which allows to switch on such substitution.
By default it is switched off, because VOPS projects may be not synchronized with original table and query on projection may return different result.
Right now projections can be automatically substituted only if:
</p>
<ul>
<li>Query doesn't contain joins.</li>
<li>Query performs aggregation of vector (tile) columns.</li>
<li>All other expressions in target list, <code>ORDER BY</code> / <code>GROUP BY</code> clauses refers only to scalar attributes of projection.</li>
</ul>
<p>
Projection can be removed using <code>drop_projection(projection_name text)</code> function.
It not only drops the correspondent table, but also removes information about it from <code>vops_partitions</code> table
and drops generated refresh function.
</p>
<h2><a name="example">Example</a></h2>
<p>
The most popular benchmark for OLAP is <a href="http://www.tpc.org/tpch">TPC-H</a>.
It contains 21 different queries.
We adopted for VOPS only two of them: Q1 and Q6 which are not using joins.
Most of fragments of this code are already mentioned above, but here we collect it together:
</p>
<pre>
-- Standard way of creating extension
create extension vops;
-- Original TPC-H table
create table lineitem(
l_orderkey integer,
l_partkey integer,
l_suppkey integer,
l_linenumber integer,
l_quantity real,
l_extendedprice real,
l_discount real,
l_tax real,
l_returnflag "char",
l_linestatus "char",
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment char(44),
l_dummy char(1)); -- this table is needed because of terminator after last column in generated data
-- Import data to it
copy lineitem from '/mnt/data/lineitem.tbl' delimiter '|' csv;
-- Create VOPS projection
create table vops_lineitem(
l_shipdate vops_date not null,
l_quantity vops_float4 not null,
l_extendedprice vops_float4 not null,
l_discount vops_float4 not null,
l_tax vops_float4 not null,
l_returnflag vops_char not null,
l_linestatus vops_char not null
);
-- Copy data to the projection table
select populate(destination := 'vops_lineitem'::regclass, source := 'lineitem'::regclass);
-- For honest comparison creates the same projection without VOPS types
create table lineitem_projection as (select l_shipdate,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag::"char",l_linestatus::"char" from lineitem);
-- Now create mixed projection with partitioning keys:
create table vops_lineitem_projection(
l_shipdate vops_date not null,
l_quantity vops_float4 not null,
l_extendedprice vops_float4 not null,
l_discount vops_float4 not null,
l_tax vops_float4 not null,
l_returnflag "char" not null,
l_linestatus "char" not null
);
-- And populate it with data sorted by partitioning key:
select populate(destination := 'vops_lineitem_projection'::regclass, source := 'lineitem_projection'::regclass, sort := 'l_returnflag,l_linestatus');
-- Let's measure time
\timing
-- Original Q6 query performing filtering with calculation of grand aggregate
select
sum(l_extendedprice*l_discount) as revenue
from
lineitem
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
-- VOPS version of Q6 using VOPS specific operators
select sum(l_extendedprice*l_discount) as revenue
from vops_lineitem
where filter(betwixt(l_shipdate, '1996-01-01', '1997-01-01')
& betwixt(l_discount, 0.08, 0.1)
& (l_quantity < 24));
-- Yet another vectorized version of Q6, but now in stadnard SQL:
select sum(l_extendedprice*l_discount) as revenue
from vops_lineitem
where l_shipdate between '1996-01-01'::date AND '1997-01-01'::date
and l_discount between 0.08 and 0.1
and l_quantity < 24;
-- Original version of Q1: filter + group by + aggregation
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= '1998-12-01'
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
-- VOPS version of Q1, sorry - no final sorting
select reduce(map(l_returnflag||l_linestatus, 'sum,sum,sum,sum,avg,avg,avg',
l_quantity,
l_extendedprice,
l_extendedprice*(1-l_discount),
l_extendedprice*(1-l_discount)*(1+l_tax),
l_quantity,
l_extendedprice,
l_discount)) from vops_lineitem where filter(l_shipdate <= '1998-12-01'::date);
-- Mixed mode: let's Postgres does group by and calculates VOPS aggregates for each group
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
vops_lineitem_projection
where
l_shipdate <= '1998-12-01'::date
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
</pre>
<h2><a name="performance">Performance evaluation</a></h2>
<p>
Now most interesting thing: compare performance results on original table and using vector operations on VOPS projection.
All measurements were performed at desktop with 16Gb of RAM and quad-core i7-4770 CPU @ 3.40GHz processor with enabled hyper-threading.