I have a table with 2 milion rows and 10 node-cockroach cluster. Select query is taking more than 10 seconds ( sometimes 30 seconds) which is a lot compared to PostgreSQL. With same set of indexes on table, PostgreSQL returns the result in few milliseconds. Here are my table definition and indexes,
CREATE TABLE test (
id integer NOT NULL,
type integer NOT NULL,
updatetimestamp timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (id, type, updatetimestamp)
create index test_timestamp_idx on test(updatetimestamp);
The query that is being executed:
SELECT id, type, updatetimestamp FROM test WHERE updatetimestamp >= 1523555640::TIMESTAMP WITH TIME ZONE ORDER BY updatetimestamp
- I am executing this query from node1 cockroach console.
Please suggest if I am missing anything in terms of optimization.