storage/db: optimize /uploads queries without query string
This uses a different query structure for ListUploads when the query
string is empty. On our production database, this reduces the query
time from ~5s to ~0.1s, which drastically improves the performance of
perf.golang.org.
Change-Id: I1f2bf97449858d1b06791dd352cc437dcbda1fac
Reviewed-on: https://go-review.googlesource.com/37168
Run-TryBot: Quentin Smith <quentin@golang.org>
TryBot-Result: Gobot Gobot <gobot@golang.org>
Reviewed-by: Russ Cox <rsc@golang.org>
diff --git a/storage/db/db.go b/storage/db/db.go
index 9cb7745..a53931e 100644
--- a/storage/db/db.go
+++ b/storage/db/db.go
@@ -590,33 +590,37 @@
query += fmt.Sprintf(", (SELECT l%d.Value FROM RecordLabels l%d WHERE l%d.UploadID = j.UploadID AND Name = ? LIMIT 1)", i, i, i)
args = append(args, label)
}
- query += " FROM (SELECT UploadID, COUNT(*) as rCount FROM "
sql, qArgs, err := parseQuery(q)
if err != nil {
ret.err = err
return ret
}
- args = append(args, qArgs...)
- for i, part := range sql {
- if i > 0 {
- query += " INNER JOIN "
+ if len(sql) == 0 {
+ // Optimize empty query.
+ query += " FROM (SELECT UploadID, (SELECT COUNT(*) FROM Records r WHERE r.UploadID = u.UploadID) AS rCount FROM Uploads u ORDER BY u.Day DESC, u.Seq DESC, u.UploadID DESC"
+ if limit != 0 {
+ query += fmt.Sprintf(" LIMIT %d", limit)
}
- query += fmt.Sprintf("(%s) t%d", part, i)
- if i > 0 {
- query += " USING (UploadID, RecordID)"
+ query += ") j"
+ } else {
+ // Join individual queries.
+ query += " FROM (SELECT UploadID, COUNT(*) as rCount FROM "
+ args = append(args, qArgs...)
+ for i, part := range sql {
+ if i > 0 {
+ query += " INNER JOIN "
+ }
+ query += fmt.Sprintf("(%s) t%d", part, i)
+ if i > 0 {
+ query += " USING (UploadID, RecordID)"
+ }
}
- }
- if len(sql) > 0 {
- query += " LEFT JOIN"
- }
- query += " Records r"
- if len(sql) > 0 {
- query += " USING (UploadID, RecordID)"
- }
- query += " GROUP BY UploadID) j LEFT JOIN Uploads u USING (UploadID) ORDER BY u.Day DESC, u.Seq DESC, u.UploadID DESC"
- if limit != 0 {
- query += fmt.Sprintf(" LIMIT %d", limit)
+ query += " LEFT JOIN Records r USING (UploadID, RecordID)"
+ query += " GROUP BY UploadID) j LEFT JOIN Uploads u USING (UploadID) ORDER BY u.Day DESC, u.Seq DESC, u.UploadID DESC"
+ if limit != 0 {
+ query += fmt.Sprintf(" LIMIT %d", limit)
+ }
}
ret.sqlQuery, ret.sqlArgs = query, args
diff --git a/storage/db/db_test.go b/storage/db/db_test.go
index 6101098..76b8adf 100644
--- a/storage/db/db_test.go
+++ b/storage/db/db_test.go
@@ -392,7 +392,10 @@
for _, test := range tests {
t.Run(fmt.Sprintf("query=%s/limit=%d", test.query, test.limit), func(t *testing.T) {
r := db.ListUploads(test.query, test.extraLabels, test.limit)
- defer r.Close()
+ defer func() {
+ t.Logf("r.Debug: %s", r.Debug())
+ r.Close()
+ }()
var have []result
for r.Next() {
ui := r.Info()
