search

storj / dbx

A neat codegen-based database wrapper written in Go
Apache License 2.0
24 stars 3 forks source link

do more efficient tuple comparisons in Spanner #11

Closed egonelbre closed 1 month ago

egonelbre commented 1 month ago

Currently for spanner tuple comparisons we do this with repeated arguments:

(a > ?) OR
(a = ? AND b > ?) OR
(a = ? AND b = ? AND c > ?)

This is problematic for two reasons -- there are more where conditions than necessary, and there are quite a few repeated arguments. The database may not figure out a good query, because it doesn't know that some of the arguments are exactly the same.

However this can be more efficiently implemented as:

(a > $1) OR (a = $1 AND ( b > $2 OR (b = $2 AND c > $3)))

Even without positional arguments it should be an improvement. There's a partial implementation in https://review.dev.storj.io/c/storj/dbx/+/13866, however, it will require a different way of creating the arguments for the query.

See https://review.dev.storj.io/c/storj/dbx/+/13866/1/testrun/paged_composite/paged_composite.dbx.go for an example difference between queries.

storj-gerrit[bot] commented 1 month ago

Change sql: optimize Spanner tuple comparison mentions this issue.

egonelbre commented 1 month ago

It still could use positional arguments, however that's more difficult to implement.