Re:“SQL queries ran up to 29 times faster on CrateDB than th...
by jodok on Friday May 05, 2017 @01:17PM
(#14275066)
thanks for the comment, we'll update the indexes used in postgres.
we tried to be as objective as possible and of course show our sweet spot. we don't want to treat postgres unfair, if you have suggestions on how to improve indexing in postgres we'd be happy to take that into account. (full disclosure: i'm co-founder of crate.io).
So I downloaded the mentioned whitepaper to look at the indexes they created in PostgreSQL.
The only thing mentioned in the whitepaper is this table definition:
The table definition in Crate is a little different: I don't know anything about how Crate works, but I see PARTITIONED BY ("week_generated") and CLUSTERED BY ("tenant_id") INTO 3 SHARDS.Now looking at the first query, both, their PARTITION BY as well as CLUSTER BY columns appear in the query:
Unless there is a sufficiently good index used in PostgreSQL, this doesn't seem to be a fair benchmark.In the Appendix they mention how the partitioning could be implemented in PostgreSQL, also mention the indexes to be used together with the partitioning, but also say: "Because of this, we abandoned the partitioning approach for our benchmark, instead opting for a single PostgreSQL table with no partition logic."
I didn't find any statement about if/which indexes they were using in PostgreSQL when measuring their benchmarks.
They also use a rather old PostgreSQL version (9.2 — released in 2012). Now, we have PostgreSQL 9.6 which has some parallel query execution support that might also change this figures dramatically.