Skip to content

Instantly share code, notes, and snippets.

@cswinter

cswinter/locustdb-lz4-vs-nolz4

Created August 12, 2018 17:35
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
Raw
locustdb-lz4-vs-nolz4
➜ ~ uname -a
Linux Archon 4.4.0-66-generic #87-Ubuntu SMP Fri Mar 3 15:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
➜ ~ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 12
On-line CPU(s) list: 0-11
Thread(s) per core: 2
Core(s) per socket: 6
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 63
Model name: Intel(R) Core(TM) i7-5820K CPU @ 3.30GHz
Stepping: 2
CPU MHz: 2201.332
CPU max MHz: 3600.0000
CPU min MHz: 1200.0000
BogoMIPS: 6596.19
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 15360K
NUMA node0 CPU(s): 0-11
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm epb tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid cqm xsaveopt cqm_llc cqm_occup_llc dtherm ida arat pln pts
➜ ~ ulimit -n 4096
➜ ~ sudo cpupower frequency-set -g performance
[sudo] password for clemens:
Setting cpu: 0
Setting cpu: 1
Setting cpu: 2
Setting cpu: 3
Setting cpu: 4
Setting cpu: 5
Setting cpu: 6
Setting cpu: 7
Setting cpu: 8
Setting cpu: 9
Setting cpu: 10
Setting cpu: 11
➜ ~ dstat
You did not select any stats, using -cdngy by default.
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
8 1 90 1 0 0| 11M 5293k| 0 0 | 0 0 |2309 5587
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 79 183
0 0 100 0 0 0| 0 44k| 309B 0 | 0 0 | 25 63
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 25 64
1 0 99 0 0 0| 148k 8472k| 13k 3447B| 0 0 | 189 332
0 0 100 0 0 0| 0 4096B| 309B 0 | 0 0 | 32 83
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 22 56
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 33 170
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 36 90
0 0 100 0 0 0| 0 76k| 373B 64B| 0 0 | 61 120
0 0 100 0 0 0| 0 8192B| 309B 0 | 0 0 | 31 94
0 0 100 0 0 0| 0 0 | 379B 70B| 0 0 | 39 74
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 31 66
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 21 72
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 18 46 ^
0 0 100 0 0 0| 0 4096B| 309B 0 | 0 0 | 35 150
0 0 100 0 0 0| 0 0 | 387B 90B| 0 0 | 52 158
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 52 251 ^C
0 0 100 0 0 0| 0 0 | 309B 0 | 0 0 | 41 166
0 0 100 0 0 0| 0 88k| 309B 0 | 0 0 | 70 204 ^C
➜ ~ cd src/LocustDB
➜ LocustDB git:(master) ✗ git commit -a --amend
[master 6bcbfcb] Stripping lz4 from identity codec panics
Date: Sat Aug 11 20:53:33 2018 -0700
1 file changed, 1 insertion(+), 1 deletion(-)
➜ LocustDB git:(master) git rev-parse HEAD
6bcbfcba3093c98ce2a062c493a855eff45acacb
➜ LocustDB git:(master) RUSTFLAGS="-g -Ccodegen-units=1" CARGO_INCREMENTAL=0 cargo run --release --bin repl --features 'enable_lz4 enable_rocksdb' -- --db-path /home/clemens/ldb-20 --mem-limit-tables=45
Finished release [optimized] target(s) in 0.09s
Running `target/release/repl --db-path /home/clemens/ldb-20 --mem-limit-tables=45`
# Table `trips` (1464785771 rows, 12.0KiB) #
dropoff_borocode: 0.00B
max_temperature: 0.00B
total_amount: 0.00B
pickup_cdeligibil: 0.00B
dropoff_cdeligibil: 0.00B
rate_code_id: 0.00B
min_temperature: 0.00B
dropoff_latitude: 0.00B
store_and_fwd_flag: 0.00B
extra: 0.00B
pickup_nyct2010_gid: 0.00B
passenger_count: 0.00B
fare_amount: 0.00B
pickup_puma: 0.00B
tolls_amount: 0.00B
pickup_borocode: 0.00B
pickup: 0.00B
pickup_ctlabel: 0.00B
pickup_datetime: 0.00B
dropoff_ntacode: 0.00B
trip_type: 0.00B
vendor_id: 0.00B
pickup_boroct2010: 0.00B
trip_id: 0.00B
average_wind_speed: 0.00B
pickup_ct2010: 0.00B
ehail_fee: 0.00B
dropoff_datetime: 0.00B
snowfall: 0.00B
snow_depth: 0.00B
dropoff_ntaname: 0.00B
precipitation: 0.00B
trip_distance: 0.00B
dropoff_ct2010: 0.00B
payment_type: 0.00B
dropoff_nyct2010_gid: 0.00B
tip_amount: 0.00B
cab_type: 0.00B
dropoff_puma: 0.00B
dropoff: 0.00B
pickup_longitude: 0.00B
dropoff_longitude: 0.00B
pickup_ntacode: 0.00B
dropoff_boroname: 0.00B
pickup_latitude: 0.00B
dropoff_ctlabel: 0.00B
pickup_ntaname: 0.00B
improvement_surcharge: 0.00B
mta_tax: 0.00B
pickup_boroname: 0.00B
dropoff_boroct2010: 0.00B
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 569ms (2.6 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 255ms (5.7 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 252ms (5.8 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 253ms (5.8 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 1.97s (0.74 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 554ms (2.6 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 552ms (2.7 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 552ms (2.7 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 2.30s (0.64 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 544ms (2.7 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 546ms (2.7 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 543ms (2.7 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), trip_distance / 1000, count(0) FROM trips;
Scanned 1.46 billion rows in 4.87s (0.30 billion rows/s)!
passenger_count | col_0 | col_1 | count_0
----------------+-------+-------+---------
0 | 2009 | 0 | 570
0 | 2009 | 1 | 267
0 | 2009 | 2 | 115
0 | 2009 | 3 | 52
0 | 2009 | 4 | 28
0 | 2009 | 5 | 28
0 | 2009 | 6 | 13
0 | 2009 | 7 | 11
0 | 2009 | 8 | 8
0 | 2009 | 9 | 5
0 | 2009 | 10 | 4
0 | 2009 | 11 | 6
0 | 2009 | 12 | 2
0 | 2009 | 13 | 2
0 | 2009 | 14 | 2
0 | 2009 | 17 | 3
0 | 2009 | 18 | 2
0 | 2009 | 19 | 2
0 | 2009 | 20 | 1
0 | 2009 | 21 | 2
0 | 2009 | 22 | 2
0 | 2009 | 31 | 1
0 | 2010 | 0 | 16834
0 | 2010 | 1 | 10264
0 | 2010 | 2 | 3858
0 | 2010 | 3 | 1797
0 | 2010 | 4 | 831
0 | 2010 | 5 | 643
0 | 2010 | 6 | 418
0 | 2010 | 7 | 207
0 | 2010 | 8 | 108
0 | 2010 | 9 | 100
0 | 2010 | 10 | 109
0 | 2010 | 11 | 118
0 | 2010 | 12 | 58
0 | 2010 | 13 | 30
0 | 2010 | 14 | 20
0 | 2010 | 15 | 17
0 | 2010 | 16 | 13
0 | 2010 | 17 | 10
0 | 2010 | 18 | 3
0 | 2010 | 19 | 8
0 | 2010 | 20 | 8
0 | 2010 | 21 | 5
0 | 2010 | 22 | 2
0 | 2010 | 23 | 3
0 | 2010 | 24 | 2
0 | 2010 | 26 | 3
0 | 2010 | 27 | 1
0 | 2010 | 28 | 1
0 | 2010 | 29 | 1
0 | 2010 | 32 | 1
0 | 2010 | 33 | 1
0 | 2010 | 36 | 2
0 | 2010 | 37 | 2
0 | 2010 | 38 | 1
0 | 2010 | 43 | 1
0 | 2010 | 47 | 1
0 | 2010 | 53 | 1
0 | 2010 | 54 | 1
0 | 2010 | 70 | 1
0 | 2010 | 76 | 1
0 | 2010 | 77 | 1
0 | 2010 | 86 | 1
0 | 2011 | 0 | 679047
0 | 2011 | 1 | 884997
0 | 2011 | 2 | 430866
0 | 2011 | 3 | 211773
0 | 2011 | 4 | 112552
0 | 2011 | 5 | 69700
0 | 2011 | 6 | 43498
0 | 2011 | 7 | 26703
0 | 2011 | 8 | 16962
0 | 2011 | 9 | 13569
0 | 2011 | 10 | 10974
0 | 2011 | 11 | 8656
0 | 2011 | 12 | 6488
0 | 2011 | 13 | 4422
0 | 2011 | 14 | 3534
0 | 2011 | 15 | 3470
0 | 2011 | 16 | 4541
0 | 2011 | 17 | 4617
0 | 2011 | 18 | 3805
0 | 2011 | 19 | 2574
0 | 2011 | 20 | 1710
0 | 2011 | 21 | 965
0 | 2011 | 22 | 581
0 | 2011 | 23 | 340
0 | 2011 | 24 | 214
0 | 2011 | 25 | 189
0 | 2011 | 26 | 153
0 | 2011 | 27 | 138
0 | 2011 | 28 | 114
0 | 2011 | 29 | 63
0 | 2011 | 30 | 73
0 | 2011 | 31 | 58
0 | 2011 | 32 | 72
0 | 2011 | 33 | 39
0 | 2011 | 34 | 46
0 | 2011 | 35 | 39
locustdb> SELECT passenger_count, to_year(pickup_datetime), trip_distance / 1000, count(0) FROM trips;
Scanned 1.46 billion rows in 3.10s (0.47 billion rows/s)!
passenger_count | col_0 | col_1 | count_0
----------------+-------+-------+---------
0 | 2009 | 0 | 570
0 | 2009 | 1 | 267
0 | 2009 | 2 | 115
0 | 2009 | 3 | 52
0 | 2009 | 4 | 28
0 | 2009 | 5 | 28
0 | 2009 | 6 | 13
0 | 2009 | 7 | 11
0 | 2009 | 8 | 8
0 | 2009 | 9 | 5
0 | 2009 | 10 | 4
0 | 2009 | 11 | 6
0 | 2009 | 12 | 2
0 | 2009 | 13 | 2
0 | 2009 | 14 | 2
0 | 2009 | 17 | 3
0 | 2009 | 18 | 2
0 | 2009 | 19 | 2
0 | 2009 | 20 | 1
0 | 2009 | 21 | 2
0 | 2009 | 22 | 2
0 | 2009 | 31 | 1
0 | 2010 | 0 | 16834
0 | 2010 | 1 | 10264
0 | 2010 | 2 | 3858
0 | 2010 | 3 | 1797
0 | 2010 | 4 | 831
0 | 2010 | 5 | 643
0 | 2010 | 6 | 418
0 | 2010 | 7 | 207
0 | 2010 | 8 | 108
0 | 2010 | 9 | 100
0 | 2010 | 10 | 109
0 | 2010 | 11 | 118
0 | 2010 | 12 | 58
0 | 2010 | 13 | 30
0 | 2010 | 14 | 20
0 | 2010 | 15 | 17
0 | 2010 | 16 | 13
0 | 2010 | 17 | 10
0 | 2010 | 18 | 3
0 | 2010 | 19 | 8
0 | 2010 | 20 | 8
0 | 2010 | 21 | 5
0 | 2010 | 22 | 2
0 | 2010 | 23 | 3
0 | 2010 | 24 | 2
0 | 2010 | 26 | 3
0 | 2010 | 27 | 1
0 | 2010 | 28 | 1
0 | 2010 | 29 | 1
0 | 2010 | 32 | 1
0 | 2010 | 33 | 1
0 | 2010 | 36 | 2
0 | 2010 | 37 | 2
0 | 2010 | 38 | 1
0 | 2010 | 43 | 1
0 | 2010 | 47 | 1
0 | 2010 | 53 | 1
0 | 2010 | 54 | 1
0 | 2010 | 70 | 1
0 | 2010 | 76 | 1
0 | 2010 | 77 | 1
0 | 2010 | 86 | 1
0 | 2011 | 0 | 679047
0 | 2011 | 1 | 884997
0 | 2011 | 2 | 430866
0 | 2011 | 3 | 211773
0 | 2011 | 4 | 112552
0 | 2011 | 5 | 69700
0 | 2011 | 6 | 43498
0 | 2011 | 7 | 26703
0 | 2011 | 8 | 16962
0 | 2011 | 9 | 13569
0 | 2011 | 10 | 10974
0 | 2011 | 11 | 8656
0 | 2011 | 12 | 6488
0 | 2011 | 13 | 4422
0 | 2011 | 14 | 3534
0 | 2011 | 15 | 3470
0 | 2011 | 16 | 4541
0 | 2011 | 17 | 4617
0 | 2011 | 18 | 3805
0 | 2011 | 19 | 2574
0 | 2011 | 20 | 1710
0 | 2011 | 21 | 965
0 | 2011 | 22 | 581
0 | 2011 | 23 | 340
0 | 2011 | 24 | 214
0 | 2011 | 25 | 189
0 | 2011 | 26 | 153
0 | 2011 | 27 | 138
0 | 2011 | 28 | 114
0 | 2011 | 29 | 63
0 | 2011 | 30 | 73
0 | 2011 | 31 | 58
0 | 2011 | 32 | 72
0 | 2011 | 33 | 39
0 | 2011 | 34 | 46
0 | 2011 | 35 | 39
locustdb> SELECT passenger_count, to_year(pickup_datetime), trip_distance / 1000, count(0) FROM trips;
Scanned 1.46 billion rows in 3.10s (0.47 billion rows/s)!
passenger_count | col_0 | col_1 | count_0
----------------+-------+-------+---------
0 | 2009 | 0 | 570
0 | 2009 | 1 | 267
0 | 2009 | 2 | 115
0 | 2009 | 3 | 52
0 | 2009 | 4 | 28
0 | 2009 | 5 | 28
0 | 2009 | 6 | 13
0 | 2009 | 7 | 11
0 | 2009 | 8 | 8
0 | 2009 | 9 | 5
0 | 2009 | 10 | 4
0 | 2009 | 11 | 6
0 | 2009 | 12 | 2
0 | 2009 | 13 | 2
0 | 2009 | 14 | 2
0 | 2009 | 17 | 3
0 | 2009 | 18 | 2
0 | 2009 | 19 | 2
0 | 2009 | 20 | 1
0 | 2009 | 21 | 2
0 | 2009 | 22 | 2
0 | 2009 | 31 | 1
0 | 2010 | 0 | 16834
0 | 2010 | 1 | 10264
0 | 2010 | 2 | 3858
0 | 2010 | 3 | 1797
0 | 2010 | 4 | 831
0 | 2010 | 5 | 643
0 | 2010 | 6 | 418
0 | 2010 | 7 | 207
0 | 2010 | 8 | 108
0 | 2010 | 9 | 100
0 | 2010 | 10 | 109
0 | 2010 | 11 | 118
0 | 2010 | 12 | 58
0 | 2010 | 13 | 30
0 | 2010 | 14 | 20
0 | 2010 | 15 | 17
0 | 2010 | 16 | 13
0 | 2010 | 17 | 10
0 | 2010 | 18 | 3
0 | 2010 | 19 | 8
0 | 2010 | 20 | 8
0 | 2010 | 21 | 5
0 | 2010 | 22 | 2
0 | 2010 | 23 | 3
0 | 2010 | 24 | 2
0 | 2010 | 26 | 3
0 | 2010 | 27 | 1
0 | 2010 | 28 | 1
0 | 2010 | 29 | 1
0 | 2010 | 32 | 1
0 | 2010 | 33 | 1
0 | 2010 | 36 | 2
0 | 2010 | 37 | 2
0 | 2010 | 38 | 1
0 | 2010 | 43 | 1
0 | 2010 | 47 | 1
0 | 2010 | 53 | 1
0 | 2010 | 54 | 1
0 | 2010 | 70 | 1
0 | 2010 | 76 | 1
0 | 2010 | 77 | 1
0 | 2010 | 86 | 1
0 | 2011 | 0 | 679047
0 | 2011 | 1 | 884997
0 | 2011 | 2 | 430866
0 | 2011 | 3 | 211773
0 | 2011 | 4 | 112552
0 | 2011 | 5 | 69700
0 | 2011 | 6 | 43498
0 | 2011 | 7 | 26703
0 | 2011 | 8 | 16962
0 | 2011 | 9 | 13569
0 | 2011 | 10 | 10974
0 | 2011 | 11 | 8656
0 | 2011 | 12 | 6488
0 | 2011 | 13 | 4422
0 | 2011 | 14 | 3534
0 | 2011 | 15 | 3470
0 | 2011 | 16 | 4541
0 | 2011 | 17 | 4617
0 | 2011 | 18 | 3805
0 | 2011 | 19 | 2574
0 | 2011 | 20 | 1710
0 | 2011 | 21 | 965
0 | 2011 | 22 | 581
0 | 2011 | 23 | 340
0 | 2011 | 24 | 214
0 | 2011 | 25 | 189
0 | 2011 | 26 | 153
0 | 2011 | 27 | 138
0 | 2011 | 28 | 114
0 | 2011 | 29 | 63
0 | 2011 | 30 | 73
0 | 2011 | 31 | 58
0 | 2011 | 32 | 72
0 | 2011 | 33 | 39
0 | 2011 | 34 | 46
0 | 2011 | 35 | 39
locustdb> SELECT passenger_count, to_year(pickup_datetime), trip_distance / 1000, count(0) FROM trips;
Scanned 1.46 billion rows in 3.10s (0.47 billion rows/s)!
passenger_count | col_0 | col_1 | count_0
----------------+-------+-------+---------
0 | 2009 | 0 | 570
0 | 2009 | 1 | 267
0 | 2009 | 2 | 115
0 | 2009 | 3 | 52
0 | 2009 | 4 | 28
0 | 2009 | 5 | 28
0 | 2009 | 6 | 13
0 | 2009 | 7 | 11
0 | 2009 | 8 | 8
0 | 2009 | 9 | 5
0 | 2009 | 10 | 4
0 | 2009 | 11 | 6
0 | 2009 | 12 | 2
0 | 2009 | 13 | 2
0 | 2009 | 14 | 2
0 | 2009 | 17 | 3
0 | 2009 | 18 | 2
0 | 2009 | 19 | 2
0 | 2009 | 20 | 1
0 | 2009 | 21 | 2
0 | 2009 | 22 | 2
0 | 2009 | 31 | 1
0 | 2010 | 0 | 16834
0 | 2010 | 1 | 10264
0 | 2010 | 2 | 3858
0 | 2010 | 3 | 1797
0 | 2010 | 4 | 831
0 | 2010 | 5 | 643
0 | 2010 | 6 | 418
0 | 2010 | 7 | 207
0 | 2010 | 8 | 108
0 | 2010 | 9 | 100
0 | 2010 | 10 | 109
0 | 2010 | 11 | 118
0 | 2010 | 12 | 58
0 | 2010 | 13 | 30
0 | 2010 | 14 | 20
0 | 2010 | 15 | 17
0 | 2010 | 16 | 13
0 | 2010 | 17 | 10
0 | 2010 | 18 | 3
0 | 2010 | 19 | 8
0 | 2010 | 20 | 8
0 | 2010 | 21 | 5
0 | 2010 | 22 | 2
0 | 2010 | 23 | 3
0 | 2010 | 24 | 2
0 | 2010 | 26 | 3
0 | 2010 | 27 | 1
0 | 2010 | 28 | 1
0 | 2010 | 29 | 1
0 | 2010 | 32 | 1
0 | 2010 | 33 | 1
0 | 2010 | 36 | 2
0 | 2010 | 37 | 2
0 | 2010 | 38 | 1
0 | 2010 | 43 | 1
0 | 2010 | 47 | 1
0 | 2010 | 53 | 1
0 | 2010 | 54 | 1
0 | 2010 | 70 | 1
0 | 2010 | 76 | 1
0 | 2010 | 77 | 1
0 | 2010 | 86 | 1
0 | 2011 | 0 | 679047
0 | 2011 | 1 | 884997
0 | 2011 | 2 | 430866
0 | 2011 | 3 | 211773
0 | 2011 | 4 | 112552
0 | 2011 | 5 | 69700
0 | 2011 | 6 | 43498
0 | 2011 | 7 | 26703
0 | 2011 | 8 | 16962
0 | 2011 | 9 | 13569
0 | 2011 | 10 | 10974
0 | 2011 | 11 | 8656
0 | 2011 | 12 | 6488
0 | 2011 | 13 | 4422
0 | 2011 | 14 | 3534
0 | 2011 | 15 | 3470
0 | 2011 | 16 | 4541
0 | 2011 | 17 | 4617
0 | 2011 | 18 | 3805
0 | 2011 | 19 | 2574
0 | 2011 | 20 | 1710
0 | 2011 | 21 | 965
0 | 2011 | 22 | 581
0 | 2011 | 23 | 340
0 | 2011 | 24 | 214
0 | 2011 | 25 | 189
0 | 2011 | 26 | 153
0 | 2011 | 27 | 138
0 | 2011 | 28 | 114
0 | 2011 | 29 | 63
0 | 2011 | 30 | 73
0 | 2011 | 31 | 58
0 | 2011 | 32 | 72
0 | 2011 | 33 | 39
0 | 2011 | 34 | 46
0 | 2011 | 35 | 39
locustdb> SELECT trip_id FROM trips WHERE (passenger_count = 5) AND (vendor_id = "CMT") AND (total_amount < 500)
AND (store_and_fwd_flag = "1") LIMIT 100;
Scanned 1.46 billion rows in 3.24s (0.45 billion rows/s)!
trip_id
----------
201367926
154865525
155088848
293214555
228237325
174687424
175280990
161072070
208684890
208926304
237368106
237523596
237547051
193071605
102569125
177367538
83385404
91804982
299069329
259106549
208072881
210730145
310082405
139768745
244996620
106309860
261961948
138080491
138089339
194235323
80845388
166444891
190937722
190953060
191366388
255415775
241317351
245281086
263677077
202610735
203089568
232337094
163106718
176394104
189096372
195778208
195827261
137556872
156105032
212714740
85701057
257680549
130612502
195132906
103186939
250557151
303171377
303600048
113478507
207059403
182124046
97545508
203844240
136472715
136644714
142031633
112520068
106048938
147380973
148254686
247696432
233908085
84895468
104895642
298515659
237961482
173799760
174182597
164837200
144660880
122800644
181868551
243127062
152363765
121789437
121970701
122012280
135130551
locustdb> SELECT trip_id FROM trips WHERE (passenger_count = 5) AND (vendor_id = "CMT") AND (total_amount < 500)
AND (store_and_fwd_flag = "1") LIMIT 100;
Scanned 1.46 billion rows in 1.56s (0.94 billion rows/s)!
trip_id
----------
106309860
193071605
255415775
194235323
103186939
174687424
175280990
161072070
203844240
208684890
208926304
139768745
104895642
303171377
303600048
102569125
228237325
97545508
181868551
189096372
233908085
212714740
173799760
174182597
310082405
190937722
190953060
191366388
298515659
207059403
85701057
177367538
176394104
144660880
91804982
244996620
202610735
203089568
237961482
154865525
155088848
83385404
152363765
135130551
237368106
237523596
237547051
263677077
232337094
182124046
210730145
259106549
84895468
156105032
136472715
136644714
257680549
250557151
106048938
166444891
147380973
148254686
80845388
121789437
121970701
122012280
113478507
243127062
299069329
208072881
130612502
195132906
142031633
241317351
195778208
195827261
164837200
122800644
137556872
261961948
201367926
293214555
138080491
138089339
163106718
245281086
112520068
247696432
locustdb> SELECT trip_id FROM trips WHERE (passenger_count = 5) AND (vendor_id = "CMT") AND (total_amount < 500)
AND (store_and_fwd_flag = "1") LIMIT 100;
Scanned 1.46 billion rows in 1.56s (0.94 billion rows/s)!
trip_id
----------
255415775
202610735
203089568
237961482
194235323
154865525
155088848
103186939
97545508
245281086
203844240
84895468
173799760
174182597
102569125
176394104
208684890
208926304
139768745
201367926
195132906
174687424
175280990
244996620
193071605
293214555
106048938
147380973
148254686
190937722
190953060
191366388
156105032
210730145
241317351
91804982
310082405
104895642
303171377
303600048
232337094
182124046
163106718
80845388
121789437
121970701
122012280
298515659
195778208
195827261
164837200
144660880
259106549
261961948
212714740
130612502
228237325
189096372
166444891
135130551
299069329
106309860
233908085
138080491
138089339
243127062
83385404
122800644
237368106
237523596
237547051
136472715
136644714
85701057
142031633
250557151
161072070
112520068
137556872
263677077
113478507
207059403
208072881
257680549
177367538
181868551
152363765
247696432
locustdb> SELECT trip_id FROM trips WHERE (passenger_count = 5) AND (vendor_id = "CMT") AND (total_amount < 500)
AND (store_and_fwd_flag = "1") LIMIT 100;
Scanned 1.46 billion rows in 1.57s (0.94 billion rows/s)!
trip_id
----------
106309860
255415775
202610735
203089568
156105032
130612502
195778208
195827261
164837200
122800644
250557151
243127062
166444891
152363765
147380973
148254686
193071605
182124046
112520068
144660880
203844240
263677077
298515659
177367538
189096372
310082405
232337094
173799760
174182597
102569125
208684890
208926304
139768745
244996620
190937722
190953060
191366388
259106549
84895468
163106718
176394104
91804982
293214555
194235323
195132906
174687424
175280990
83385404
299069329
201367926
154865525
155088848
103186939
181868551
135130551
233908085
261961948
104895642
303171377
303600048
212714740
138080491
138089339
80845388
161072070
207059403
208072881
257680549
228237325
210730145
241317351
137556872
237961482
97545508
245281086
121789437
121970701
122012280
247696432
237368106
237523596
237547051
113478507
136472715
136644714
85701057
142031633
106048938
locustdb> SELECT passenger_count, trip_distance, total_amount FROM trips ORDER BY total_amount DESC LIMIT 100;
Scanned 1.46 billion rows in 222ms (6.6 billion rows/s)!
passenger_count | trip_distance | total_amount
----------------+---------------+--------------
1 | 5320 | 395061160
1 | 180 | 98997039
2 | 9600 | 86161105
1 | 700 | 86160324
1 | 0 | 82603997
1 | 0 | 82603997
1 | 11620 | 68590810
1 | 1160 | 65026285
2 | 0 | 64756743
1 | 2200 | 63112198
1 | 800 | 63046312
1 | 0 | 62903378
1 | 0 | 62903378
3 | 0 | 62590160
5 | 1170 | 54846335
5 | 1800 | 54143256
1 | 0 | 53858000
1 | 0 | 53848268
2 | 0 | 50530481
1 | 1300 | 50332633
1 | 0 | 42956225
1 | 4300 | 42781990
1 | 0 | 41026766
1 | 0 | 40848698
1 | 1178600 | 40411466
1 | 0 | 39847996
1 | 0 | 39846670
2 | 0 | 39846620
1 | 0 | 39322232
1 | 1000 | 39191358
1 | 0 | 37918374
3 | 2200 | 36177202
1 | 0 | 33886334
2 | 1100 | 33554521
1 | 700 | 33541449
1 | 0 | 33340289
1 | 258800 | 32549603
1 | 0 | 32274382
1 | 45600 | 30704518
2 | 0 | 26908936
1 | 1200 | 24861943
1 | 0 | 21831709
1 | 0 | 21052379
1 | 0 | 20214955
1 | 1000 | 20054751
1 | 6800 | 19864438
2 | 2700 | 18744576
1 | 2100 | 18744426
2 | 1800 | 18744226
1 | 1800 | 18613254
2 | 3700 | 17186358
1 | 600 | 17186246
1 | 2800 | 17185699
1 | 0 | 16868839
1 | 0 | 16868789
1 | 0 | 16606012
2 | 12500 | 15900116
1 | 298914200 | 15483214
1 | 151694000 | 15329622
1 | 1714056400 | 15327117
6 | 300 | 15243838
1 | 4400 | 15099774
1 | 0 | 13690599
3 | 166331600 | 13313120
2 | 166086300 | 13312500
1 | 158600 | 13306368
2 | 131091400 | 12636658
1 | 1000 | 12451927
1 | 0 | 11605382
1 | 900 | 11127165
2 | 500 | 10778818
3 | 0 | 10744509
3 | 0 | 10108073
1 | 7500 | 9397810
2 | 1500 | 9396416
1 | 198623013600 | 9395569
1 | 536862699 | 9395270
1 | 390288800 | 9150945
1 | 13100 | 8897786
2 | 1180 | 8806493
6 | 1190 | 8633055
2 | 522133000 | 8224197
1 | 3140000000 | 7579732
1 | 1530 | 6995607
1 | 288703900 | 6155303
1 | 2200 | 5119200
1 | 100 | 3736032
1 | 357894700 | 3153439
1 | 0 | 2361976
1 | 5240 | 2254411
1 | 234410600 | 2245986
1 | 400 | 2097476
1 | 71732700 | 2004407
1 | 0 | 1401030
2 | 733400 | 1289125
4 | 0 | 1275534
3 | 0 | 1248541
1 | 1524200 | 1018021
1 | 1514910 | 1000099
5 | 0 | 1000099
locustdb> SELECT passenger_count, trip_distance, total_amount FROM trips ORDER BY total_amount DESC LIMIT 100;
Scanned 1.46 billion rows in 221ms (6.6 billion rows/s)!
passenger_count | trip_distance | total_amount
----------------+---------------+--------------
1 | 5320 | 395061160
1 | 180 | 98997039
2 | 9600 | 86161105
1 | 700 | 86160324
1 | 0 | 82603997
1 | 0 | 82603997
1 | 11620 | 68590810
1 | 1160 | 65026285
2 | 0 | 64756743
1 | 2200 | 63112198
1 | 800 | 63046312
1 | 0 | 62903378
1 | 0 | 62903378
3 | 0 | 62590160
5 | 1170 | 54846335
5 | 1800 | 54143256
1 | 0 | 53858000
1 | 0 | 53848268
2 | 0 | 50530481
1 | 1300 | 50332633
1 | 0 | 42956225
1 | 4300 | 42781990
1 | 0 | 41026766
1 | 0 | 40848698
1 | 1178600 | 40411466
1 | 0 | 39847996
1 | 0 | 39846670
2 | 0 | 39846620
1 | 0 | 39322232
1 | 1000 | 39191358
1 | 0 | 37918374
3 | 2200 | 36177202
1 | 0 | 33886334
2 | 1100 | 33554521
1 | 700 | 33541449
1 | 0 | 33340289
1 | 258800 | 32549603
1 | 0 | 32274382
1 | 45600 | 30704518
2 | 0 | 26908936
1 | 1200 | 24861943
1 | 0 | 21831709
1 | 0 | 21052379
1 | 0 | 20214955
1 | 1000 | 20054751
1 | 6800 | 19864438
2 | 2700 | 18744576
1 | 2100 | 18744426
2 | 1800 | 18744226
1 | 1800 | 18613254
2 | 3700 | 17186358
1 | 600 | 17186246
1 | 2800 | 17185699
1 | 0 | 16868839
1 | 0 | 16868789
1 | 0 | 16606012
2 | 12500 | 15900116
1 | 298914200 | 15483214
1 | 151694000 | 15329622
1 | 1714056400 | 15327117
6 | 300 | 15243838
1 | 4400 | 15099774
1 | 0 | 13690599
3 | 166331600 | 13313120
2 | 166086300 | 13312500
1 | 158600 | 13306368
2 | 131091400 | 12636658
1 | 1000 | 12451927
1 | 0 | 11605382
1 | 900 | 11127165
2 | 500 | 10778818
3 | 0 | 10744509
3 | 0 | 10108073
1 | 7500 | 9397810
2 | 1500 | 9396416
1 | 198623013600 | 9395569
1 | 536862699 | 9395270
1 | 390288800 | 9150945
1 | 13100 | 8897786
2 | 1180 | 8806493
6 | 1190 | 8633055
2 | 522133000 | 8224197
1 | 3140000000 | 7579732
1 | 1530 | 6995607
1 | 288703900 | 6155303
1 | 2200 | 5119200
1 | 100 | 3736032
1 | 357894700 | 3153439
1 | 0 | 2361976
1 | 5240 | 2254411
1 | 234410600 | 2245986
1 | 400 | 2097476
1 | 71732700 | 2004407
1 | 0 | 1401030
2 | 733400 | 1289125
4 | 0 | 1275534
3 | 0 | 1248541
1 | 1524200 | 1018021
1 | 1514910 | 1000099
5 | 0 | 1000099
locustdb> SELECT passenger_count, trip_distance, total_amount FROM trips ORDER BY total_amount DESC LIMIT 100;
Scanned 1.46 billion rows in 221ms (6.6 billion rows/s)!
passenger_count | trip_distance | total_amount
----------------+---------------+--------------
1 | 5320 | 395061160
1 | 180 | 98997039
2 | 9600 | 86161105
1 | 700 | 86160324
1 | 0 | 82603997
1 | 0 | 82603997
1 | 11620 | 68590810
1 | 1160 | 65026285
2 | 0 | 64756743
1 | 2200 | 63112198
1 | 800 | 63046312
1 | 0 | 62903378
1 | 0 | 62903378
3 | 0 | 62590160
5 | 1170 | 54846335
5 | 1800 | 54143256
1 | 0 | 53858000
1 | 0 | 53848268
2 | 0 | 50530481
1 | 1300 | 50332633
1 | 0 | 42956225
1 | 4300 | 42781990
1 | 0 | 41026766
1 | 0 | 40848698
1 | 1178600 | 40411466
1 | 0 | 39847996
1 | 0 | 39846670
2 | 0 | 39846620
1 | 0 | 39322232
1 | 1000 | 39191358
1 | 0 | 37918374
3 | 2200 | 36177202
1 | 0 | 33886334
2 | 1100 | 33554521
1 | 700 | 33541449
1 | 0 | 33340289
1 | 258800 | 32549603
1 | 0 | 32274382
1 | 45600 | 30704518
2 | 0 | 26908936
1 | 1200 | 24861943
1 | 0 | 21831709
1 | 0 | 21052379
1 | 0 | 20214955
1 | 1000 | 20054751
1 | 6800 | 19864438
2 | 2700 | 18744576
1 | 2100 | 18744426
2 | 1800 | 18744226
1 | 1800 | 18613254
2 | 3700 | 17186358
1 | 600 | 17186246
1 | 2800 | 17185699
1 | 0 | 16868839
1 | 0 | 16868789
1 | 0 | 16606012
2 | 12500 | 15900116
1 | 298914200 | 15483214
1 | 151694000 | 15329622
1 | 1714056400 | 15327117
6 | 300 | 15243838
1 | 4400 | 15099774
1 | 0 | 13690599
3 | 166331600 | 13313120
2 | 166086300 | 13312500
1 | 158600 | 13306368
2 | 131091400 | 12636658
1 | 1000 | 12451927
1 | 0 | 11605382
1 | 900 | 11127165
2 | 500 | 10778818
3 | 0 | 10744509
3 | 0 | 10108073
1 | 7500 | 9397810
2 | 1500 | 9396416
1 | 198623013600 | 9395569
1 | 536862699 | 9395270
1 | 390288800 | 9150945
1 | 13100 | 8897786
2 | 1180 | 8806493
6 | 1190 | 8633055
2 | 522133000 | 8224197
1 | 3140000000 | 7579732
1 | 1530 | 6995607
1 | 288703900 | 6155303
1 | 2200 | 5119200
1 | 100 | 3736032
1 | 357894700 | 3153439
1 | 0 | 2361976
1 | 5240 | 2254411
1 | 234410600 | 2245986
1 | 400 | 2097476
1 | 71732700 | 2004407
1 | 0 | 1401030
2 | 733400 | 1289125
4 | 0 | 1275534
3 | 0 | 1248541
1 | 1524200 | 1018021
1 | 1514910 | 1000099
5 | 0 | 1000099
locustdb> SELECT passenger_count, trip_distance, total_amount FROM trips ORDER BY total_amount DESC LIMIT 100;
Scanned 1.46 billion rows in 220ms (6.7 billion rows/s)!
passenger_count | trip_distance | total_amount
----------------+---------------+--------------
1 | 5320 | 395061160
1 | 180 | 98997039
2 | 9600 | 86161105
1 | 700 | 86160324
1 | 0 | 82603997
1 | 0 | 82603997
1 | 11620 | 68590810
1 | 1160 | 65026285
2 | 0 | 64756743
1 | 2200 | 63112198
1 | 800 | 63046312
1 | 0 | 62903378
1 | 0 | 62903378
3 | 0 | 62590160
5 | 1170 | 54846335
5 | 1800 | 54143256
1 | 0 | 53858000
1 | 0 | 53848268
2 | 0 | 50530481
1 | 1300 | 50332633
1 | 0 | 42956225
1 | 4300 | 42781990
1 | 0 | 41026766
1 | 0 | 40848698
1 | 1178600 | 40411466
1 | 0 | 39847996
1 | 0 | 39846670
2 | 0 | 39846620
1 | 0 | 39322232
1 | 1000 | 39191358
1 | 0 | 37918374
3 | 2200 | 36177202
1 | 0 | 33886334
2 | 1100 | 33554521
1 | 700 | 33541449
1 | 0 | 33340289
1 | 258800 | 32549603
1 | 0 | 32274382
1 | 45600 | 30704518
2 | 0 | 26908936
1 | 1200 | 24861943
1 | 0 | 21831709
1 | 0 | 21052379
1 | 0 | 20214955
1 | 1000 | 20054751
1 | 6800 | 19864438
2 | 2700 | 18744576
1 | 2100 | 18744426
2 | 1800 | 18744226
1 | 1800 | 18613254
2 | 3700 | 17186358
1 | 600 | 17186246
1 | 2800 | 17185699
1 | 0 | 16868839
1 | 0 | 16868789
1 | 0 | 16606012
2 | 12500 | 15900116
1 | 298914200 | 15483214
1 | 151694000 | 15329622
1 | 1714056400 | 15327117
6 | 300 | 15243838
1 | 4400 | 15099774
1 | 0 | 13690599
3 | 166331600 | 13313120
2 | 166086300 | 13312500
1 | 158600 | 13306368
2 | 131091400 | 12636658
1 | 1000 | 12451927
1 | 0 | 11605382
1 | 900 | 11127165
2 | 500 | 10778818
3 | 0 | 10744509
3 | 0 | 10108073
1 | 7500 | 9397810
2 | 1500 | 9396416
1 | 198623013600 | 9395569
1 | 536862699 | 9395270
1 | 390288800 | 9150945
1 | 13100 | 8897786
2 | 1180 | 8806493
6 | 1190 | 8633055
2 | 522133000 | 8224197
1 | 3140000000 | 7579732
1 | 1530 | 6995607
1 | 288703900 | 6155303
1 | 2200 | 5119200
1 | 100 | 3736032
1 | 357894700 | 3153439
1 | 0 | 2361976
1 | 5240 | 2254411
1 | 234410600 | 2245986
1 | 400 | 2097476
1 | 71732700 | 2004407
1 | 0 | 1401030
2 | 733400 | 1289125
4 | 0 | 1275534
3 | 0 | 1248541
1 | 1524200 | 1018021
1 | 1514910 | 1000099
5 | 0 | 1000099
locustdb> SELECT pickup_puma, dropoff_puma, passenger_count, count(0) from trips;
Scanned 1.46 billion rows in 5.28s (0.28 billion rows/s)!
pickup_puma | dropoff_puma | passenger_count | count_0
------------+--------------+-----------------+-----------
0 | 0 | 0 | 239636
0 | 0 | 1 | 158504430
0 | 0 | 2 | 30589460
0 | 0 | 3 | 8775405
0 | 0 | 4 | 4182591
0 | 0 | 5 | 11051643
0 | 0 | 6 | 6082326
0 | 0 | 7 | 1168
0 | 0 | 8 | 1107
0 | 0 | 9 | 703
0 | 0 | 10 | 14
0 | 0 | 36 | 1
0 | 0 | 47 | 1
0 | 0 | 49 | 26
0 | 0 | 58 | 2
0 | 0 | 70 | 1
0 | 0 | 84 | 1
0 | 0 | 113 | 1
0 | 0 | 134 | 1
0 | 0 | 137 | 1
0 | 0 | 141 | 1
0 | 0 | 155 | 1
0 | 0 | 158 | 1
0 | 0 | 160 | 1
0 | 0 | 163 | 1
0 | 0 | 164 | 1
0 | 0 | 165 | 1
0 | 0 | 192 | 1
0 | 0 | 208 | 812
0 | 0 | 213 | 4
0 | 0 | 225 | 1
0 | 0 | 247 | 1
0 | 0 | 255 | 6
0 | 3701 | 0 | 13
0 | 3701 | 1 | 3593
0 | 3701 | 2 | 760
0 | 3701 | 3 | 209
0 | 3701 | 4 | 69
0 | 3701 | 5 | 417
0 | 3701 | 6 | 56
0 | 3701 | 7 | 2
0 | 3701 | 8 | 2
0 | 3701 | 9 | 1
0 | 3702 | 0 | 12
0 | 3702 | 1 | 2706
0 | 3702 | 2 | 511
0 | 3702 | 3 | 131
0 | 3702 | 4 | 56
0 | 3702 | 5 | 291
0 | 3702 | 6 | 58
0 | 3702 | 8 | 1
0 | 3703 | 0 | 5
0 | 3703 | 1 | 3673
0 | 3703 | 2 | 796
0 | 3703 | 3 | 237
0 | 3703 | 4 | 95
0 | 3703 | 5 | 614
0 | 3703 | 6 | 47
0 | 3703 | 8 | 1
0 | 3704 | 0 | 13
0 | 3704 | 1 | 1850
0 | 3704 | 2 | 390
0 | 3704 | 3 | 81
0 | 3704 | 4 | 43
0 | 3704 | 5 | 164
0 | 3704 | 6 | 23
0 | 3704 | 8 | 2
0 | 3705 | 0 | 13
0 | 3705 | 1 | 2913
0 | 3705 | 2 | 594
0 | 3705 | 3 | 186
0 | 3705 | 4 | 69
0 | 3705 | 5 | 317
0 | 3705 | 6 | 43
0 | 3705 | 8 | 1
0 | 3706 | 0 | 9
0 | 3706 | 1 | 2551
0 | 3706 | 2 | 469
0 | 3706 | 3 | 122
0 | 3706 | 4 | 39
0 | 3706 | 5 | 211
0 | 3706 | 6 | 28
0 | 3706 | 8 | 4
0 | 3706 | 9 | 1
0 | 3707 | 0 | 8
0 | 3707 | 1 | 2695
0 | 3707 | 2 | 523
0 | 3707 | 3 | 167
0 | 3707 | 4 | 67
0 | 3707 | 5 | 255
0 | 3707 | 6 | 34
0 | 3707 | 8 | 1
0 | 3708 | 0 | 16
0 | 3708 | 1 | 4899
0 | 3708 | 2 | 1045
0 | 3708 | 3 | 310
0 | 3708 | 4 | 128
0 | 3708 | 5 | 768
0 | 3708 | 6 | 50
0 | 3708 | 7 | 3
locustdb> SELECT pickup_puma, dropoff_puma, passenger_count, count(0) from trips;
Scanned 1.46 billion rows in 4.16s (0.35 billion rows/s)!
pickup_puma | dropoff_puma | passenger_count | count_0
------------+--------------+-----------------+-----------
0 | 0 | 0 | 239636
0 | 0 | 1 | 158504430
0 | 0 | 2 | 30589460
0 | 0 | 3 | 8775405
0 | 0 | 4 | 4182591
0 | 0 | 5 | 11051643
0 | 0 | 6 | 6082326
0 | 0 | 7 | 1168
0 | 0 | 8 | 1107
0 | 0 | 9 | 703
0 | 0 | 10 | 14
0 | 0 | 36 | 1
0 | 0 | 47 | 1
0 | 0 | 49 | 26
0 | 0 | 58 | 2
0 | 0 | 70 | 1
0 | 0 | 84 | 1
0 | 0 | 113 | 1
0 | 0 | 134 | 1
0 | 0 | 137 | 1
0 | 0 | 141 | 1
0 | 0 | 155 | 1
0 | 0 | 158 | 1
0 | 0 | 160 | 1
0 | 0 | 163 | 1
0 | 0 | 164 | 1
0 | 0 | 165 | 1
0 | 0 | 192 | 1
0 | 0 | 208 | 812
0 | 0 | 213 | 4
0 | 0 | 225 | 1
0 | 0 | 247 | 1
0 | 0 | 255 | 6
0 | 3701 | 0 | 13
0 | 3701 | 1 | 3593
0 | 3701 | 2 | 760
0 | 3701 | 3 | 209
0 | 3701 | 4 | 69
0 | 3701 | 5 | 417
0 | 3701 | 6 | 56
0 | 3701 | 7 | 2
0 | 3701 | 8 | 2
0 | 3701 | 9 | 1
0 | 3702 | 0 | 12
0 | 3702 | 1 | 2706
0 | 3702 | 2 | 511
0 | 3702 | 3 | 131
0 | 3702 | 4 | 56
0 | 3702 | 5 | 291
0 | 3702 | 6 | 58
0 | 3702 | 8 | 1
0 | 3703 | 0 | 5
0 | 3703 | 1 | 3673
0 | 3703 | 2 | 796
0 | 3703 | 3 | 237
0 | 3703 | 4 | 95
0 | 3703 | 5 | 614
0 | 3703 | 6 | 47
0 | 3703 | 8 | 1
0 | 3704 | 0 | 13
0 | 3704 | 1 | 1850
0 | 3704 | 2 | 390
0 | 3704 | 3 | 81
0 | 3704 | 4 | 43
0 | 3704 | 5 | 164
0 | 3704 | 6 | 23
0 | 3704 | 8 | 2
0 | 3705 | 0 | 13
0 | 3705 | 1 | 2913
0 | 3705 | 2 | 594
0 | 3705 | 3 | 186
0 | 3705 | 4 | 69
0 | 3705 | 5 | 317
0 | 3705 | 6 | 43
0 | 3705 | 8 | 1
0 | 3706 | 0 | 9
0 | 3706 | 1 | 2551
0 | 3706 | 2 | 469
0 | 3706 | 3 | 122
0 | 3706 | 4 | 39
0 | 3706 | 5 | 211
0 | 3706 | 6 | 28
0 | 3706 | 8 | 4
0 | 3706 | 9 | 1
0 | 3707 | 0 | 8
0 | 3707 | 1 | 2695
0 | 3707 | 2 | 523
0 | 3707 | 3 | 167
0 | 3707 | 4 | 67
0 | 3707 | 5 | 255
0 | 3707 | 6 | 34
0 | 3707 | 8 | 1
0 | 3708 | 0 | 16
0 | 3708 | 1 | 4899
0 | 3708 | 2 | 1045
0 | 3708 | 3 | 310
0 | 3708 | 4 | 128
0 | 3708 | 5 | 768
0 | 3708 | 6 | 50
0 | 3708 | 7 | 3
locustdb> SELECT pickup_puma, dropoff_puma, passenger_count, count(0) from trips;
Scanned 1.46 billion rows in 4.17s (0.35 billion rows/s)!
pickup_puma | dropoff_puma | passenger_count | count_0
------------+--------------+-----------------+-----------
0 | 0 | 0 | 239636
0 | 0 | 1 | 158504430
0 | 0 | 2 | 30589460
0 | 0 | 3 | 8775405
0 | 0 | 4 | 4182591
0 | 0 | 5 | 11051643
0 | 0 | 6 | 6082326
0 | 0 | 7 | 1168
0 | 0 | 8 | 1107
0 | 0 | 9 | 703
0 | 0 | 10 | 14
0 | 0 | 36 | 1
0 | 0 | 47 | 1
0 | 0 | 49 | 26
0 | 0 | 58 | 2
0 | 0 | 70 | 1
0 | 0 | 84 | 1
0 | 0 | 113 | 1
0 | 0 | 134 | 1
0 | 0 | 137 | 1
0 | 0 | 141 | 1
0 | 0 | 155 | 1
0 | 0 | 158 | 1
0 | 0 | 160 | 1
0 | 0 | 163 | 1
0 | 0 | 164 | 1
0 | 0 | 165 | 1
0 | 0 | 192 | 1
0 | 0 | 208 | 812
0 | 0 | 213 | 4
0 | 0 | 225 | 1
0 | 0 | 247 | 1
0 | 0 | 255 | 6
0 | 3701 | 0 | 13
0 | 3701 | 1 | 3593
0 | 3701 | 2 | 760
0 | 3701 | 3 | 209
0 | 3701 | 4 | 69
0 | 3701 | 5 | 417
0 | 3701 | 6 | 56
0 | 3701 | 7 | 2
0 | 3701 | 8 | 2
0 | 3701 | 9 | 1
0 | 3702 | 0 | 12
0 | 3702 | 1 | 2706
0 | 3702 | 2 | 511
0 | 3702 | 3 | 131
0 | 3702 | 4 | 56
0 | 3702 | 5 | 291
0 | 3702 | 6 | 58
0 | 3702 | 8 | 1
0 | 3703 | 0 | 5
0 | 3703 | 1 | 3673
0 | 3703 | 2 | 796
0 | 3703 | 3 | 237
0 | 3703 | 4 | 95
0 | 3703 | 5 | 614
0 | 3703 | 6 | 47
0 | 3703 | 8 | 1
0 | 3704 | 0 | 13
0 | 3704 | 1 | 1850
0 | 3704 | 2 | 390
0 | 3704 | 3 | 81
0 | 3704 | 4 | 43
0 | 3704 | 5 | 164
0 | 3704 | 6 | 23
0 | 3704 | 8 | 2
0 | 3705 | 0 | 13
0 | 3705 | 1 | 2913
0 | 3705 | 2 | 594
0 | 3705 | 3 | 186
0 | 3705 | 4 | 69
0 | 3705 | 5 | 317
0 | 3705 | 6 | 43
0 | 3705 | 8 | 1
0 | 3706 | 0 | 9
0 | 3706 | 1 | 2551
0 | 3706 | 2 | 469
0 | 3706 | 3 | 122
0 | 3706 | 4 | 39
0 | 3706 | 5 | 211
0 | 3706 | 6 | 28
0 | 3706 | 8 | 4
0 | 3706 | 9 | 1
0 | 3707 | 0 | 8
0 | 3707 | 1 | 2695
0 | 3707 | 2 | 523
0 | 3707 | 3 | 167
0 | 3707 | 4 | 67
0 | 3707 | 5 | 255
0 | 3707 | 6 | 34
0 | 3707 | 8 | 1
0 | 3708 | 0 | 16
0 | 3708 | 1 | 4899
0 | 3708 | 2 | 1045
0 | 3708 | 3 | 310
0 | 3708 | 4 | 128
0 | 3708 | 5 | 768
0 | 3708 | 6 | 50
0 | 3708 | 7 | 3
locustdb> SELECT pickup_puma, dropoff_puma, passenger_count, count(0) from trips;
Scanned 1.46 billion rows in 4.17s (0.35 billion rows/s)!
pickup_puma | dropoff_puma | passenger_count | count_0
------------+--------------+-----------------+-----------
0 | 0 | 0 | 239636
0 | 0 | 1 | 158504430
0 | 0 | 2 | 30589460
0 | 0 | 3 | 8775405
0 | 0 | 4 | 4182591
0 | 0 | 5 | 11051643
0 | 0 | 6 | 6082326
0 | 0 | 7 | 1168
0 | 0 | 8 | 1107
0 | 0 | 9 | 703
0 | 0 | 10 | 14
0 | 0 | 36 | 1
0 | 0 | 47 | 1
0 | 0 | 49 | 26
0 | 0 | 58 | 2
0 | 0 | 70 | 1
0 | 0 | 84 | 1
0 | 0 | 113 | 1
0 | 0 | 134 | 1
0 | 0 | 137 | 1
0 | 0 | 141 | 1
0 | 0 | 155 | 1
0 | 0 | 158 | 1
0 | 0 | 160 | 1
0 | 0 | 163 | 1
0 | 0 | 164 | 1
0 | 0 | 165 | 1
0 | 0 | 192 | 1
0 | 0 | 208 | 812
0 | 0 | 213 | 4
0 | 0 | 225 | 1
0 | 0 | 247 | 1
0 | 0 | 255 | 6
0 | 3701 | 0 | 13
0 | 3701 | 1 | 3593
0 | 3701 | 2 | 760
0 | 3701 | 3 | 209
0 | 3701 | 4 | 69
0 | 3701 | 5 | 417
0 | 3701 | 6 | 56
0 | 3701 | 7 | 2
0 | 3701 | 8 | 2
0 | 3701 | 9 | 1
0 | 3702 | 0 | 12
0 | 3702 | 1 | 2706
0 | 3702 | 2 | 511
0 | 3702 | 3 | 131
0 | 3702 | 4 | 56
0 | 3702 | 5 | 291
0 | 3702 | 6 | 58
0 | 3702 | 8 | 1
0 | 3703 | 0 | 5
0 | 3703 | 1 | 3673
0 | 3703 | 2 | 796
0 | 3703 | 3 | 237
0 | 3703 | 4 | 95
0 | 3703 | 5 | 614
0 | 3703 | 6 | 47
0 | 3703 | 8 | 1
0 | 3704 | 0 | 13
0 | 3704 | 1 | 1850
0 | 3704 | 2 | 390
0 | 3704 | 3 | 81
0 | 3704 | 4 | 43
0 | 3704 | 5 | 164
0 | 3704 | 6 | 23
0 | 3704 | 8 | 2
0 | 3705 | 0 | 13
0 | 3705 | 1 | 2913
0 | 3705 | 2 | 594
0 | 3705 | 3 | 186
0 | 3705 | 4 | 69
0 | 3705 | 5 | 317
0 | 3705 | 6 | 43
0 | 3705 | 8 | 1
0 | 3706 | 0 | 9
0 | 3706 | 1 | 2551
0 | 3706 | 2 | 469
0 | 3706 | 3 | 122
0 | 3706 | 4 | 39
0 | 3706 | 5 | 211
0 | 3706 | 6 | 28
0 | 3706 | 8 | 4
0 | 3706 | 9 | 1
0 | 3707 | 0 | 8
0 | 3707 | 1 | 2695
0 | 3707 | 2 | 523
0 | 3707 | 3 | 167
0 | 3707 | 4 | 67
0 | 3707 | 5 | 255
0 | 3707 | 6 | 34
0 | 3707 | 8 | 1
0 | 3708 | 0 | 16
0 | 3708 | 1 | 4899
0 | 3708 | 2 | 1045
0 | 3708 | 3 | 310
0 | 3708 | 4 | 128
0 | 3708 | 5 | 768
0 | 3708 | 6 | 50
0 | 3708 | 7 | 3
locustdb>
➜ LocustDB git:(master) RUSTFLAGS="-g -Ccodegen-units=1" CARGO_INCREMENTAL=0 cargo run --release --bin repl --features 'enable_lz4 enable_rocksdb' -- --db-path /home/clemens/ldb-20 --mem-limit-tables=45 --mem-lz4
Finished release [optimized] target(s) in 0.09s
Running `target/release/repl --db-path /home/clemens/ldb-20 --mem-limit-tables=45 --mem-lz4`
# Table `trips` (1464785771 rows, 12.0KiB) #
pickup_ntacode: 0.00B
pickup_datetime: 0.00B
dropoff: 0.00B
trip_distance: 0.00B
fare_amount: 0.00B
tip_amount: 0.00B
pickup_boroct2010: 0.00B
snow_depth: 0.00B
pickup_longitude: 0.00B
max_temperature: 0.00B
payment_type: 0.00B
dropoff_ntaname: 0.00B
trip_type: 0.00B
pickup_ctlabel: 0.00B
dropoff_boroct2010: 0.00B
dropoff_borocode: 0.00B
snowfall: 0.00B
min_temperature: 0.00B
pickup_puma: 0.00B
improvement_surcharge: 0.00B
tolls_amount: 0.00B
vendor_id: 0.00B
precipitation: 0.00B
extra: 0.00B
pickup_latitude: 0.00B
trip_id: 0.00B
total_amount: 0.00B
pickup_cdeligibil: 0.00B
pickup_ntaname: 0.00B
mta_tax: 0.00B
passenger_count: 0.00B
pickup_nyct2010_gid: 0.00B
ehail_fee: 0.00B
pickup_boroname: 0.00B
rate_code_id: 0.00B
pickup_borocode: 0.00B
dropoff_puma: 0.00B
dropoff_latitude: 0.00B
dropoff_ctlabel: 0.00B
dropoff_datetime: 0.00B
dropoff_boroname: 0.00B
dropoff_ct2010: 0.00B
dropoff_longitude: 0.00B
dropoff_ntacode: 0.00B
cab_type: 0.00B
pickup_ct2010: 0.00B
dropoff_cdeligibil: 0.00B
average_wind_speed: 0.00B
store_and_fwd_flag: 0.00B
dropoff_nyct2010_gid: 0.00B
pickup: 0.00B
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 536ms (2.7 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 309ms (4.7 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 308ms (4.8 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT cab_type, count(0) FROM trips;
Scanned 1.46 billion rows in 308ms (4.7 billion rows/s)!
cab_type | count_0
---------+------------
"green" | 64407776
"yellow" | 1400377995
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 1.88s (0.78 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 884ms (1.7 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 884ms (1.7 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, count(0), sum(total_amount) FROM trips;
Scanned 1.46 billion rows in 881ms (1.7 billion rows/s)!
passenger_count | count_0 | sum_1
----------------+------------+---------------
0 | 4107443 | 4509842206
1 | 1028187597 | 1425303818216
2 | 210577025 | 304718353489
3 | 62345389 | 87788492212
4 | 29994987 | 42514355874
5 | 95184622 | 130181002616
6 | 34380600 | 51063741303
7 | 2595 | 8693795
8 | 2525 | 8272055
9 | 1367 | 7196690
10 | 17 | 75470
13 | 1 | 3150
15 | 2 | 2410
17 | 1 | 3995
19 | 1 | 500
25 | 1 | 760
33 | 2 | 1719
34 | 1 | 1680
36 | 1 | 6155
37 | 1 | 1420
38 | 1 | 730
47 | 1 | 900
49 | 26 | 8440
53 | 1 | 730
58 | 2 | 5188
61 | 1 | 3134
65 | 3 | 7010
66 | 1 | 1930
69 | 1 | 580
70 | 1 | 1070
84 | 1 | 4384
97 | 1 | 990
113 | 1 | 1330
125 | 1 | 1660
129 | 7 | 6150
133 | 1 | 1030
134 | 1 | 5514
137 | 1 | 5964
141 | 1 | 1894
155 | 1 | 9024
158 | 1 | 1444
160 | 1 | 1534
163 | 1 | 1554
164 | 1 | 6214
165 | 1 | 1214
177 | 1 | 1700
192 | 1 | 953
193 | 1 | 750
208 | 1508 | 1062534
211 | 1 | 700
213 | 4 | 1000
223 | 1 | 950
225 | 1 | 1600
247 | 1 | 1944
249 | 1 | 950
250 | 3 | 3770
254 | 1 | 650
255 | 10 | 17990
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 2.40s (0.61 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scanned 1.46 billion rows in 715ms (2.0 billion rows/s)!
passenger_count | col_0 | count_0
----------------+-------+-----------
0 | 2009 | 1126
0 | 2010 | 35487
0 | 2011 | 2547937
0 | 2012 | 1274858
0 | 2013 | 5476
0 | 2014 | 9848
0 | 2015 | 46953
0 | 2016 | 17829
0 | 2017 | 167929
1 | 2001 | 3
1 | 2002 | 1
1 | 2003 | 4
1 | 2008 | 33
1 | 2009 | 116050070
1 | 2010 | 98991938
1 | 2011 | 119969450
1 | 2012 | 123955717
1 | 2013 | 122918578
1 | 2014 | 129404151
1 | 2015 | 119150088
1 | 2016 | 106826192
1 | 2017 | 90921221
1 | 2018 | 150
1 | 2041 | 1
2 | 2009 | 27534617
2 | 2010 | 23053740
2 | 2011 | 27132349
2 | 2012 | 24900623
2 | 2013 | 23610698
2 | 2014 | 24221283
2 | 2015 | 22349143
2 | 2016 | 20290051
2 | 2017 | 17484514
2 | 2018 | 7
3 | 2008 | 1
3 | 2009 | 7860551
3 | 2010 | 6700468
3 | 2011 | 8015005
3 | 2012 | 7716364
3 | 2013 | 7343603
3 | 2014 | 7348686
3 | 2015 | 6580430
3 | 2016 | 5810400
3 | 2017 | 4969879
3 | 2018 | 1
3 | 2053 | 1
4 | 2009 | 3829136
4 | 2010 | 3282494
4 | 2011 | 3819715
4 | 2012 | 3752549
4 | 2013 | 3593459
4 | 2014 | 3512831
4 | 2015 | 3105718
4 | 2016 | 2770237
4 | 2017 | 2328848
5 | 2008 | 7
5 | 2009 | 14885624
5 | 2010 | 12311339
5 | 2011 | 13876061
5 | 2012 | 12063339
5 | 2013 | 10139454
5 | 2014 | 10056870
5 | 2015 | 8691048
5 | 2016 | 7337348
5 | 2017 | 5823513
5 | 2018 | 19
6 | 2003 | 1
6 | 2008 | 1
6 | 2009 | 734341
6 | 2010 | 595332
6 | 2011 | 1536489
6 | 2012 | 4880708
6 | 2013 | 6779135
6 | 2014 | 6396494
6 | 2015 | 5421796
6 | 2016 | 4496670
6 | 2017 | 3539630
6 | 2018 | 3
7 | 2009 | 7
7 | 2010 | 4
7 | 2011 | 8
7 | 2012 | 17
7 | 2013 | 65
7 | 2014 | 504
7 | 2015 | 599
7 | 2016 | 765
7 | 2017 | 626
8 | 2009 | 2
8 | 2010 | 1
8 | 2011 | 2
8 | 2012 | 13
8 | 2013 | 43
8 | 2014 | 464
8 | 2015 | 668
8 | 2016 | 707
8 | 2017 | 625
9 | 2009 | 1
9 | 2010 | 3
9 | 2011 | 8
9 | 2012 | 25
locustdb> SELECT passenger_count, to_year(pickup_datetime), count(0) FROM trips;
Scann