Comments (2)
I have built a somewhat representative database now with ~3M entries so going to try to do some performance comparisons for this.
from hashr.
Ok I did some tests with both replacing the VARCHAR(100) with BYTEA and BIT(256). I also tried to query both using SELECT ... WHERE ... IN ...
and using WITH ... SELECT ... JOIN
. In all tests I queried for 50 000 samples that did exist in the database and the same number of samples that did not exist in the database. The results are as follows:
VARCHAR + IN: min: 0.66, max: 0.71, avg: 0.68
VARCHAR + WITH: min: 1.08, max: 1.17, avg: 1.12
BYTEA (HEX) + IN: min: 0.84, max: 0.97, avg: 0.89
BYTEA (HEX) + WITH: min: 1.15, max: 1.29, avg: 1.20
BYTEA (RAW) + IN: min: 0.76, max: 0.88, avg: 0.80
BYTEA (RAW) + WITH: min: 1.15, max: 1.34, avg: 1.21
BIT (HEX) + IN: min: 1.00, max: 1.18, avg: 1.03
BIT (HEX) + WITH: min: 3.39, max: 3.56, avg: 3.45
I also compared the sizes of the databases:
using VARCHAR(100): 3999 MB
using BYTEA: 2741 MB
using BIT(256): 2870 MB
So in conclusion, using IN-statements are faster than joining with a temporary table using WITH. Using the BIT(256) type doesn't seem to be any good. It is also very cumbersome to work with so that can pretty much be discarded. BYTEA offers a ~1/3 reduction of the database size at the cost of about 15% lookup time. That is probably not a worthwhile trade-off considering that these databases will probably be in the <100GB range for most use-cases.
from hashr.
Related Issues (13)
- Crash with concurrent map read/write HOT 1
- Implement a small client that would query the PostgreSQL/Cloud Spanner DB
- Add --rm when running plaso container
- Investigate deb importer compression support HOT 2
- Properly clean up temporary files on importer error
- Run containers as hashr user to remove need for "sudo rm" HOT 1
- Segfault during very long run HOT 1
- Large memory consumptions with large number of input files
- Create DB schema if it doesn't exist. HOT 1
- Add SELinux policy HOT 1
- mount failed: Operation not permitted HOT 2
- Explore alternatives to `mount` for Docker compatibility in importers
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from hashr.