MAX queries can be further optimized
Description
Components
Affects versions
Fix versions
Labels
None
Environment
None
Release Notes Description
None
relates to
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
Reporter
Sitaram Vemulapalli
Sitaram VemulapalliPriority
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
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