Dolt index searchable, lookup caching

[max-hoffman]

Two main changes

• cache schema hashes on root values, so that we don't have to a table lookup to get a schema hash
• Implement sql.IndexSearchable in a way that we cache strict key lookups for a given table schema. The lifecycle for these objects is the span between ALTER TABLE statements

[max-hoffman]

benchmark

[github-actions[bot]]

@max-hoffman workflow run: https://github.com/dolthub/dolt/actions/runs/10052395591

[coffeegoddd]

@max-hoffman DOLT	test_name	from_latency_p95	to_latency_p95	is_faster
tpcc-scale-factor-1	71.83	66.84	0

test_name	server_name	server_version	tps	test_name	server_name	server_version	tps	is_faster
tpcc-scale-factor-1	dolt	09e0040686e347e23bda171a50ecdf95ae1d335f	34.17	tpcc-scale-factor-1	dolt	70d89c02e9787b2f4e55234e5706d3a454da31d1	35.65	0

[coffeegoddd]

@max-hoffman DOLT	read_tests	from_latency_median	to_latency_median	is_faster
covering_index_scan	2.97	2.97	0
groupby_scan	17.01	17.32	0
index_join	2.81	5.18	-1
index_join_scan	2.57	2.57	0
index_scan	54.83	54.83	0
oltp_point_select	0.46	0.45	0
oltp_read_only	7.56	7.56	0
select_random_points	0.77	0.75	0
select_random_ranges	0.89	0.89	0
table_scan	56.84	56.84	0
types_table_scan	147.61	142.39	0

write_tests	from_latency_median	to_latency_median	is_faster
oltp_delete_insert	6.09	6.09	0
oltp_insert	3.02	3.02	0
oltp_read_write	13.95	13.7	0
oltp_update_index	3.07	3.07	0
oltp_update_non_index	3.02	3.02	0
oltp_write_only	6.32	6.32	0
types_delete_insert	6.67	6.67	0

[max-hoffman]

benchmark

[github-actions[bot]]

@max-hoffman workflow run: https://github.com/dolthub/dolt/actions/runs/10070142151

[coffeegoddd]

@max-hoffman DOLT	test_name	from_latency_p95	to_latency_p95	is_faster
tpcc-scale-factor-1	70.55	68.05	0

test_name	server_name	server_version	tps	test_name	server_name	server_version	tps	is_faster
tpcc-scale-factor-1	dolt	10e273f27d5f502af6c7b0527f0919ea1d08aa58	34.23	tpcc-scale-factor-1	dolt	9a5ab5602c2075f2707988b139f66d00e41730cf	35.71	0

[coffeegoddd]

@max-hoffman DOLT	read_tests	from_latency_median	to_latency_median	is_faster
covering_index_scan	3.02	2.97	0
groupby_scan	17.32	17.01	0
index_join	2.81	5.28	-1
index_join_scan	2.57	2.57	0
index_scan	54.83	54.83	0
oltp_point_select	0.46	0.44	0
oltp_read_only	7.56	7.43	0
select_random_points	0.77	0.74	0
select_random_ranges	0.9	0.89	0
table_scan	56.84	55.82	0
types_table_scan	144.97	142.39	0

write_tests	from_latency_median	to_latency_median	is_faster
oltp_delete_insert	6.09	6.09	0
oltp_insert	3.02	3.02	0
oltp_read_write	13.95	13.7	0
oltp_update_index	3.07	3.07	0
oltp_update_non_index	3.02	3.02	0
oltp_write_only	6.32	6.32	0
types_delete_insert	6.67	6.67	0

[coffeegoddd]

@max-hoffman DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
291842c	ok	5937457

version	total_tests
291842c	5937457

correctness_percentage
100.0

[coffeegoddd]

@max-hoffman DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
38ee88a	ok	5937457

version	total_tests
38ee88a	5937457

correctness_percentage
100.0

[max-hoffman]

benchmark

[github-actions[bot]]

@max-hoffman workflow run: https://github.com/dolthub/dolt/actions/runs/10104002111

[coffeegoddd]

@max-hoffman DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
aea0c30	ok	5937457

version	total_tests
aea0c30	5937457

correctness_percentage
100.0

[coffeegoddd]

@max-hoffman DOLT	test_name	from_latency_p95	to_latency_p95	is_faster
tpcc-scale-factor-1	69.29	71.83	0

test_name	server_name	server_version	tps	test_name	server_name	server_version	tps	is_faster
tpcc-scale-factor-1	dolt	eed7e5d51451186430f4265cd023815670120c15	34.2	tpcc-scale-factor-1	dolt	aea0c307e033505a3f83527ac5d97334c41c0d42	36.13	0

[coffeegoddd]

@coffeegoddd DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
17d2c2f	ok	5937457

version	total_tests
17d2c2f	5937457

correctness_percentage
100.0

[coffeegoddd]

@max-hoffman DOLT	read_tests	from_latency_median	to_latency_median	is_faster
covering_index_scan	2.97	2.97	0
groupby_scan	17.32	16.71	0
index_join	2.81	2.76	0
index_join_scan	2.57	2.57	0
index_scan	54.83	53.85	0
oltp_point_select	0.46	0.42	0
oltp_read_only	7.7	7.17	0
select_random_points	0.77	0.75	0
select_random_ranges	0.9	0.89	0
table_scan	55.82	54.83	0
types_table_scan	144.97	142.39	0

write_tests	from_latency_median	to_latency_median	is_faster
oltp_delete_insert	6.09	5.99	0
oltp_insert	3.02	3.02	0
oltp_read_write	13.95	13.46	0
oltp_update_index	3.07	3.02	0
oltp_update_non_index	3.02	2.97	0
oltp_write_only	6.43	6.21	0
types_delete_insert	6.67	6.67	0

[coffeegoddd]

@max-hoffman DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
e917dca	ok	5937457

version	total_tests
e917dca	5937457

correctness_percentage
100.0

[coffeegoddd]

@max-hoffman DOLT

comparing_percentages
100.000000 to 100.000000

version	result	total
a79b488	ok	5937457

version	total_tests
a79b488	5937457

correctness_percentage
100.0

[github-actions[bot]]

@coffeegoddd DOLT	test_name	detail	row_cnt	sorted	mysql_time	sql_mult	cli_mult
batching	LOAD DATA	10000	1	0.06	1.5
batching	batch sql	10000	1	0.08	1.75
batching	by line sql	10000	1	0.08	1.75
blob	1 blob	200000	1	0.91	3.82	3.63
blob	2 blobs	200000	1	0.91	4.33	4.25
blob	no blob	200000	1	0.9	2.44	2.1
col type	datetime	200000	1	0.81	3.05	2.8
col type	varchar	200000	1	0.71	3.37	2.68
config width	2 cols	200000	1	0.79	2.72	2.11
config width	32 cols	200000	1	1.89	1.95	2.44
config width	8 cols	200000	1	0.99	2.32	2.11
pk type	float	200000	1	0.84	2.43	2.05
pk type	int	200000	1	0.77	2.64	2.19
pk type	varchar	200000	1	1.53	1.7	1.41
row count	1.6mm	1600000	1	5.74	2.92	2.43
row count	400k	400000	1	1.46	2.86	2.33
row count	800k	800000	1	2.84	2.94	2.44
secondary index	four index	200000	1	3.55	1.38	1.1
secondary index	no secondary	200000	1	0.92	2.4	2.03
secondary index	one index	200000	1	1.14	2.39	2.11
secondary index	two index	200000	1	1.98	1.76	1.44
sorting	shuffled 1mm	1000000	0	5.17	2.81	2.49
sorting	sorted 1mm	1000000	1	5.25	2.77	2.45

[github-actions[bot]]

@coffeegoddd DOLT	name	detail	mean_mult
dolt_blame_basic	system table	1.28
dolt_blame_commit_filter	system table	3.36
dolt_commit_ancestors_commit_filter	system table	0.81
dolt_commits_commit_filter	system table	0.97
dolt_diff_log_join_from_commit	system table	2.09
dolt_diff_log_join_to_commit	system table	2.1
dolt_diff_table_from_commit_filter	system table	103.63
dolt_diff_table_to_commit_filter	system table	1.06
dolt_diffs_commit_filter	system table	0.92
dolt_history_commit_filter	system table	1.2
dolt_log_commit_filter	system table	1

[github-actions[bot]]

@coffeegoddd DOLT	name	add_cnt	delete_cnt	update_cnt	latency
adds_only	60000	0	0	0.72
adds_updates_deletes	60000	60000	60000	3.81
deletes_only	0	60000	0	1.88
updates_only	0	0	60000	2.43