| package sql_test |
| |
| import ( |
| "context" |
| "strings" |
| "testing" |
| |
| "github.com/stretchr/testify/require" |
| |
| "go.skia.org/infra/go/paramtools" |
| "go.skia.org/infra/golden/go/sql" |
| "go.skia.org/infra/golden/go/sql/schema" |
| "go.skia.org/infra/golden/go/sql/sqltest" |
| "go.skia.org/infra/golden/go/types" |
| ) |
| |
| // The following benchmarks measure two different techniques for searching Traces by the keys. |
| // For each test, the "faster" technique (at least when running locally on my dev machine) |
| // is listed first. |
| |
| // Locally, this ran at about 30ms per operation for 36400 Traces. It can use the indexes and |
| // appears to do so in parallel. |
| func BenchmarkUnionIntersect_AllKeysHaveMultipleValues(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| WITH |
| Fruit AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'fruit' = '"apple"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'fruit' = '"fig"' |
| ), |
| Callsign AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"foxtrot"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"tango"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"alfa"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"does not exist"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"charlie"' |
| ), |
| Province AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'province' = '"Ontario"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'province' = '"Something"' |
| ) |
| SELECT COUNT(*) FROM ( |
| SELECT * FROM Fruit |
| INTERSECT |
| SELECT * FROM Callsign |
| INTERSECT |
| SELECT * FROM Province) x;`) |
| _ = row.Scan(&count) |
| if count != 2*4*2*1 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 100ms per operation for 36400 Traces. It has to do a full table scan. |
| func BenchmarkAndIN_AllKeysHaveMultipleValues(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT count(trace_id) FROM Traces WHERE keys -> 'fruit' IN('"apple"','"fig"') |
| AND keys -> 'callsign' IN('"foxtrot"','"tango"', '"alfa"', '"does not exist"', '"charlie"') |
| AND keys -> 'province' IN('"Ontario"','"Something"');`) |
| _ = row.Scan(&count) |
| if count != 2*4*1*2 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 30ms per operation for 36400 Traces. It can use the indexes and |
| // appears to do so in parallel. |
| func BenchmarkUnionIntersect_SomeKeysHaveASingleValue(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| WITH |
| Callsign AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"foxtrot"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"tango"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"alfa"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"does not exist"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"charlie"' |
| ) |
| SELECT COUNT(*) FROM ( |
| SELECT trace_id FROM Traces WHERE keys -> 'fruit' = '"apple"' |
| INTERSECT |
| SELECT * FROM Callsign |
| INTERSECT |
| SELECT trace_id FROM Traces WHERE keys -> 'province' = '"Ontario"') x;`) |
| _ = row.Scan(&count) |
| if count != 1*4*1*2 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 80ms per operation for 36400 Traces. It did a zig-zag join on the |
| // two single item keys and then a full search over that. |
| func BenchmarkAndIN_SomeKeysHaveASingleValue(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT count(trace_id) FROM Traces WHERE keys -> 'fruit' = '"apple"' |
| AND keys -> 'callsign' IN('"foxtrot"','"tango"', '"alfa"', '"does not exist"', '"charlie"') |
| AND keys -> 'province' = '"Ontario"';`) |
| _ = row.Scan(&count) |
| if count != 1*4*1*2 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 10ms per operation for 36400 Traces. It can use the indexes and |
| // appears to do so in parallel. |
| func BenchmarkUnionIntersect_AllKeysHaveASingleValue(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT COUNT(*) FROM ( |
| SELECT trace_id FROM Traces WHERE keys -> 'fruit' = '"apple"' |
| INTERSECT |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"foxtrot"' |
| INTERSECT |
| SELECT trace_id FROM Traces WHERE keys -> 'province' = '"Ontario"') x;`) |
| _ = row.Scan(&count) |
| if count != 1*1*1*2 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 70ms per operation for 36400 Traces. It did a zig-zag join on the |
| // keys and then a lookup join on that. |
| func BenchmarkAndIN_AllKeysHaveASingleValue(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT count(trace_id) FROM Traces WHERE keys -> 'fruit' = '"apple"' |
| AND keys -> 'callsign' = '"foxtrot"' |
| AND keys -> 'province' = '"Ontario"';`) |
| _ = row.Scan(&count) |
| if count != 1*1*1*2 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 5ms per operation for 36400 Traces. There were only 260 traces |
| // with number = "three" (this value was hand-selected for the reason of having the fewest number |
| // of traces in an attempt to find a case where AndIN was faster). The optimizer looked up that |
| // index and then joined it with the index of callsign. |
| func BenchmarkAndIN_IndexJoinPossible(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT count(trace_id) FROM Traces WHERE keys -> 'number' = '"three"' |
| AND keys -> 'callsign' IN('"foxtrot"','"tango"', '"alfa"', '"does not exist"', '"charlie"');`) |
| _ = row.Scan(&count) |
| if count != 40 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 20ms per operation for 36400 Traces. It is the same query as |
| // BenchmarkAndIN_IndexJoinPossible and shows how sometimes, UnionIntersect can be slower because |
| // it can sometimes have to fetch more data (e.g. a lot of rows based on callsign) just to throw |
| // nearly all of them away (because only a few of them have number = "three"). |
| func BenchmarkUnionIntersect_IndexJoinComparison(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| WITH |
| Callsign AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"foxtrot"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"tango"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"alfa"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"does not exist"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"charlie"' |
| ) |
| SELECT COUNT(*) FROM ( |
| SELECT trace_id FROM Traces WHERE keys -> 'number' = '"three"' |
| INTERSECT |
| SELECT * FROM Callsign) x;`) |
| _ = row.Scan(&count) |
| if count != 40 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 30ms per operation for 36400 Traces. It is the same query as |
| // BenchmarkAndIN_IndexJoinNotAlwaysFast and shows that even when using an index join, the AndIN |
| // case is not consistently faster. |
| func BenchmarkUnionIntersect_IndexJoinNotAlwaysFastComparison(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| WITH |
| Callsign AS ( |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"foxtrot"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"tango"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"alfa"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"does not exist"' |
| UNION |
| SELECT trace_id FROM Traces WHERE keys -> 'callsign' = '"charlie"' |
| ) |
| SELECT COUNT(*) FROM ( |
| SELECT trace_id FROM Traces WHERE keys -> 'number' = '"eight"' |
| INTERSECT |
| SELECT * FROM Callsign) x;`) |
| _ = row.Scan(&count) |
| if count != 440 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| // Locally, this ran at about 30ms per operation for 36400 Traces. There were 2860 traces |
| // with number = "eight", a contrasting example to the other IndexJoin benchmark. It was carried |
| // out the same way, but was much slower. |
| func BenchmarkAndIN_IndexJoinNotAlwaysFast(b *testing.B) { |
| ctx := context.Background() |
| db := sqltest.NewCockroachDBForTestsWithProductionSchema(ctx, b) |
| traces := makeTestTraceRows() |
| require.NoError(b, sqltest.BulkInsertDataTables(ctx, db, schema.Tables{ |
| Traces: traces, |
| })) |
| b.ResetTimer() |
| count := 0 |
| for i := 0; i < b.N; i++ { |
| row := db.QueryRow(ctx, ` |
| SELECT count(trace_id) FROM Traces WHERE keys -> 'number' = '"eight"' |
| AND keys -> 'callsign' IN('"foxtrot"','"tango"', '"alfa"', '"does not exist"', '"charlie"');`) |
| _ = row.Scan(&count) |
| if count != 440 { |
| panic("wrong") |
| } |
| } |
| } |
| |
| const ( |
| fruitKey = "fruit" |
| callsignKey = "callsign" |
| provinceKey = "province" |
| numberKey = "number" |
| letterKey = "letter" |
| ) |
| |
| // makeTestTraceRows makes many traces filled with keys of arbitrary data that is meant to |
| // be somewhat representative of the keys in Gold. All of the traces have certain keys |
| // (e.g. fruit, callsign, province), but only some of others (e.g. number, letter). Just like |
| // Gold, some keys have more distinct values than others. |
| func makeTestTraceRows() []schema.TraceRow { |
| // https://en.wikibooks.org/wiki/Wikijunior:Fruit_Alphabet |
| fruits := []string{"apple", "apricots", "avocados", "banana", "boysenberry", "blueberry", |
| "cherry", "cantaloupe", "clementine", "date", "dewberry", "dragon fruit", "elderberry", |
| "eggfruit", "evergreen huckleberry", "entawak", "fig", "farkleberry, finger lime", |
| "grapefruit", "grapes", "gooseberries", "guava", "honeydew", "hackberry", "imbe", |
| "jackfruit", "jambolan", "kiwi", "kumquat", "lime", "lemon", "longan", "lychee", "loquat", |
| "mango", "mulberry", "melon", "nectarine", "olive", "orange", "papaya", "persimmon", |
| "paw paw", "prickly pear", "peach", "pomegranate", "pineapple", "passion fruit", |
| "quince", "quararibea cordata", "rambutan", "raspberry", "rose hip", "star fruit", |
| "strawberry", "tomato", "tangerine", "tamarind", "ugli fruit", "uniq fruit", "ugni", |
| "vanilla bean", "voavanga", "watermelon", "wolfberry", "xigua", "ximenia", "xango", |
| "yangmei", "zig zag vine fruit", |
| } |
| // https://en.wikipedia.org/wiki/NATO_phonetic_alphabet |
| callsign := []string{"alfa", "bravo", "charlie", "delta", "echo", "foxtrot", "golf", "hotel", |
| "india", "juliett", "kilo", "lima", "mike", "november", "oscar", "papa", "quebec", "romeo", |
| "sierra", "tango", "uniform", "victor", "whiskey", "x-ray", "yankee", "zulu"} |
| // https://en.wikipedia.org/wiki/Provinces_and_territories_of_Canada |
| province := []string{"Alberta", "British Columbia", "Manitoba", "New Brunswick", |
| "Newfoundland and Labrador", "Nova Scotia", "Ontario", "Prince Edward Island", "Quebec", |
| "Saskatchewan"} |
| numbers := []string{"zero", "one", "two", "three", "four", "five", "six", "seven", "eight", |
| "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", |
| "seventeen", "eighteen", "nineteen", "many"} |
| |
| var rv []schema.TraceRow |
| addTraceRow := func(p paramtools.Params) { |
| _, traceID := sql.SerializeMap(p) |
| _, groupingID := sql.SerializeMap(paramtools.Params{ |
| types.CorpusField: "a_corpus", |
| fruitKey: p[fruitKey], |
| }) |
| rv = append(rv, schema.TraceRow{ |
| TraceID: traceID, |
| Corpus: "a_corpus", |
| GroupingID: groupingID, |
| Keys: p, |
| MatchesAnyIgnoreRule: schema.NBFalse, |
| }) |
| } |
| for _, f := range fruits { |
| for _, c := range callsign { |
| for _, p := range province { |
| idx := len(f) |
| if idx >= len(numbers) { |
| idx = len(numbers) - 1 |
| } |
| addTraceRow(paramtools.Params{ |
| types.CorpusField: "a_corpus", |
| fruitKey: f, |
| callsignKey: c, |
| provinceKey: p, |
| numberKey: numbers[idx], |
| }) |
| addTraceRow(paramtools.Params{ |
| types.CorpusField: "a_corpus", |
| fruitKey: f, |
| callsignKey: c, |
| provinceKey: p, |
| letterKey: strings.Repeat(string(f[0]), 6), |
| }) |
| } |
| } |
| } |
| return rv |
| } |
