MAX queries can be further optimized

Description

https://forums.couchbase.com/t/slow-max-query-with-predicate-that-might-be-true-or-false-against-bucket-with-tens-of-millions-of-documents/17874

CREATE INDEX uuidcountermax ON production_statedata(Type, UUID DESC); SELECT MAX(UUID) as max FROM production_statedata WHERE Type = $type_name;

Above query takes time. But below query fast

SELECT UUID as max FROM production_statedata WHERE Type = $type_name ORDER BY UUID DESC LIMIT 1;

There is no group by and MAX() argument is in DESC order and all index keys before are equality predicates when first value found it can terminate scan.

This is similar to MB-29605: Optimize MIN aggregate without group by when KeyPos > 0Closed

Components

Affects versions

Fix versions

Labels

Environment

None

Release Notes Description

None

Activity

Show:

Raju Suravarjjala October 17, 2019 at 9:24 PM

Bulk closing all invalid, duplicate and won't fix bugs. Please feel free to reopen them

Jeelan Poola August 14, 2018 at 7:22 AM

When we fix https://couchbasecloud.atlassian.net/browse/MB-29605#icft=MB-29605, MAX will also be taken care of. Hence closing it as duplicate.

Duplicate
Pinned fields
Click on the next to a field label to start pinning.

Assignee

Jeelan Poola(Deactivated)

Reporter

Priority

Instabug

Open Instabug
Created August 10, 2018 at 9:06 PM
Updated October 17, 2019 at 9:24 PM
Resolved August 14, 2018 at 7:23 AM
Instabug