Sorry, I should have given more precise details instead of asking you to look into the repo.
I’m using a containerized cockroachdb on coreos, 8 cores, 16GB ram and SSD.
Here is the schema used:
CREATE TABLE IF NOT EXISTS entityone ( entityone_id BIGSERIAL NOT NULL, time_created DATE NOT NULL DEFAULT CURRENT_DATE, PRIMARY KEY (entityone_id) )
CREATE TABLE IF NOT EXISTS entityone_status ( entityone_id BIGSERIAL NOT NULL, action_id BIGINT NOT NULL DEFAULT 1, status_id INT NOT NULL DEFAULT 1, time_created DATE NOT NULL DEFAULT CURRENT_DATE, is_latest INT NULL DEFAULT 1, UNIQUE (is_latest, entityone_id), INDEX (status_id, is_latest), CONSTRAINT es_fk_e FOREIGN KEY (entityone_id) REFERENCES entityone (entityone_id), INDEX (entityone_id) )
Here is the query:
SELECT e.entityone_id, e.time_created, es.entityone_id as status_entityone_id, es.action_id, es.status_id, es.time_created as status_time_created FROM entityone e INNER JOIN entityone_status es ON es.entityone_id = e.entityone_id AND es.is_latest = 1 WHERE 0 = 0 AND e.entityone_id = $1
Here is the problem:
On the benchmark, inserting, updating is behind other DBs, which is fine and probably usable in production for now (and it will improve for sure).
The select is a lot more problematic. My bench can only do 3 selects in 60s where SQLite can do 500000!
Then order of magnitude is not at all the same here.
Is it something already known or am I doing smtg wrong? (same query is used on all DBs)
If it is known, will it be fixed (you mentioned and I already saw many times optimization of joins)? When should I test it again?