Select query is taking more than 10 seconds

General
#1

Hi,

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.

Thanks,
Kiran

#2

Show us the result of:

“explain SELECT id, type, updatetimestamp FROM test WHERE updatetimestamp >= 1523555640::TIMESTAMP WITH TIME ZONE ORDER BY updatetimestamp”

Also I’d try to create an index on updatetimestamp, I’d experiment also in storing on that index the columns id and type.

#3

Hi @kiranonet1986! How many rows does this return? 10 seconds sounds far too long, so I think something is wrong. What sort of machines are you running on? Does it reproduce on a 1 node cluster?

The test_timestamp_idx you have should be sufficient. Can you post the result of SHOW CREATE TABLE test and EXPLAIN <your select query>?