Couchbase N1QL SELECT query using ORDER BY is very slow

0
votes

The following query is very slow:

select meta(d).id, timestamp_utc, d.headline, d.text from data_01 d where type="news" and topic="some_news" and timestamp_utc is not null order by timestamp_utc asc limit 1;

I created the following indices / indexes:

 CREATE INDEX `idx_timestamp_utc` ON `data_01`((-str_to_millis(`timestamp_utc`)))
 CREATE INDEX `idx_timestamp_utc_some_news` ON `data_01`(`timestamp_utc`) WHERE ((`type` = "news") and (`topic` = "some_news"))
 CREATE INDEX `idx_topic_and_timestamp_utc` ON `data_01`(`topic`,(-str_to_millis(`timestamp_utc`)))
 CREATE INDEX `idx_type_news_topic_some_news_timestamp_utc` ON `data_01`((-`timestamp_utc`)) WHERE ((`type` = "news") and (`topic` = "some_news"))
  • None of this gives me a better performance than 1 minute.
  • There are 3million documents in the bucket topic="some_news" has 300k docs.
  • type="news" has around 2 million docs.
  • field timestamp_utc has this format: "2017-01-01T00:00:00+00:00".
  • field headline has an average length of ~100 characters.
  • field text has an average of length ~2000 characters.

I found similar problems but non of the given fixes and suggestions do work.

Thank you for a response to solve this issue!

I'm running Couchbase 4.5.1 Enterprise Edition (build-2844)

1
indexingcouchbasen1qlnosql

1 Answers

2
votes

In CB 4.5.0 and above Try one of the following indexes and modified query below. The index can be used for any topic The query predicate on topic is equality changing order by to include topic will not change results and that matches query order same as index keys order and query will avoid sort.

CREATE INDEX `idx_some_news_timestamp_utc` ON `data_01`(`topic`,`timestamp_utc`) WHERE type = "news";
CREATE INDEX `idx_some_news_timestamp_utc_headline_text` ON `data_01`(`topic`,`timestamp_utc`,headline, text) WHERE type = "news";

SELECT meta(d).id, d.timestamp_utc, d.headline, d.text FROM data_01 d
WHERE type="news" AND topic="some_news" AND timestamp_utc IS NOT NULL
ORDER BY topic, timestamp_utc ASC LIMIT 1;