Significant query performance difference caused by a non-indexed field in the WHERE clause

[Eric24]

This query takes about 2.5 seconds and reads 200000+ records:

UPDATE ObjectStoreV MERGE {"hash":"$sha256$bQD0r3H+ENCZkxndZCT7ZxQouzD2irVsCMwZpDVDydQ=","alt":"90960b00-7c34-11ee-b8ec-ac1e027c6b7f","__updated":1724893434129} WHERE (__instance = "xxx" AND __class = "files" AND __key = "b6796830-659f-11ef-8bc7-ac1e047a9f59" AND hash IS NULL) LOCK RECORD

This query takes 0.06 seconds and reads 1 record:

UPDATE ObjectStoreV MERGE {"hash":"$sha256$bQD0r3H+ENCZkxndZCT7ZxQouzD2irVsCMwZpDVDydQ=","alt":"90960b00-7c34-11ee-b8ec-ac1e027c6b7f","__updated":1724893434129} WHERE ((__instance = "xxx" AND __class = "files" AND __key = "b6796830-659f-11ef-8bc7-ac1e047a9f59") AND hash IS NULL) LOCK RECORD

The unique, compound index includes __instance, __class, and __key only, in that order. The difference in the second example is that those fields are grouped by parenthesis, with hash being separate; while the first example just lists all four fields.

This is running on 2.2.x. I'm surprised that the query optimizer wouldn't naturally use the index to find the first list of candidates before checking the hash field, but it doesn't appear to. I know that 2.2.x is old, so my real question is if this is a known issue and if it has been fixed in 3.x.

[tglman]

Hi,

Yes in 3.x (and 3.2.x) the query engine has a better handling of indexes compared to the 2.2.x, that should cover also this case.

Regards