[Bug]: IVFFLAT Search is slow with 5 billion vector index.

[cpegeric]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

94128cd

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

I saw search mainly use single thread to run instead of using 64 CPUs.

The slowest part is running the table scan of the secondary index.

		sql := fmt.Sprintf("SELECT `%s` FROM `%s`.`%s` WHERE `%s` = %d AND `%s` IN (%s)",
			catalog.SystemSI_IVFFLAT_TblCol_Entries_pk,
			tblcfg.DbName, tblcfg.EntriesTable,
			catalog.SystemSI_IVFFLAT_TblCol_Entries_version,
			idx.Version,
			catalog.SystemSI_IVFFLAT_TblCol_Entries_id,
			instr,
		)

Expected Behavior

Try to use multi-threads to run distance function. Try to use the Brute-Force index with multiple threads support. pkg/vectorindex/brute-force

Steps to Reproduce

create 5 billion vector ivfflat index
with configuration
lists 7202
vector_l2_ops

and do search.

Let me know I can give you access to aws.

Additional information

No response

[iamlinjunhong]

是多线程跑的，dop > 1。性能慢主要是没有缓存 block
--- Execution Plan ---
Project
Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 (min=1, max=1) InputSize=8 bytes OutputSize=8 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=8 bytes (min=8 bytes, max=8 bytes)
-> Sort
Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 (min=1, max=1) InputSize=16 bytes OutputSize=8 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=8 bytes (min=8 bytes, max=8 bytes)
Sort Key: mo_ivf_alias_0.score INTERNAL
Limit: 1
-> Join
Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=1 (min=1, max=1) InputSize=24 bytes OutputSize=16 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=32.13 KiB (min=16.06 KiB, max=16.07 KiB)
Join Type: INNER
Join Cond: (ivf50m.id = mo_ivf_alias_0.pkid)
Runtime Filter Build: #[-1,0]
-> Table Scan on mydb.ivf50m
Analyze: timeConsumed=0ms waitTime=0ms inputBlocks=1 inputRows=1 outputRows=1 (min=1, max=1) InputSize=8 bytes OutputSize=8 bytes ReadSize=64.03 KiB|0 bytes|0 bytes MemorySize=16 bytes (min=8 bytes, max=8 bytes)
Runtime Filter Probe: ivf50m.id
-> Table Function on ivf_search
Analyze: timeConsumed=102ms waitTime=0ms inputRows=0 outputRows=1 (min=1, max=1) InputSize=0 bytes OutputSize=16 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=16 bytes (min=16 bytes, max=16 bytes)
Index Reader Param: Limit: 1
Project
Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 (min=1, max=1) InputSize=16 bytes OutputSize=16 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=16 bytes (min=16 bytes, max=16 bytes)
-> Sort
Analyze: timeConsumed=0ms waitTime=100ms inputRows=27 outputRows=1 (min=1, max=1) InputSize=432 bytes OutputSize=16 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=448 bytes (min=16 bytes, max=432 bytes)
Sort Key: dist_func INTERNAL
Limit: 1
-> Table Scan on mydb.__mo_index_secondary_019c9a2f-c60f-707c-b7dc-1a6870df8b38
Analyze: timeConsumed=1174ms scan_time=[total=1174ms,min=0ms,max=98ms,dop=64] waitTime=0ms inputBlocks=27 inputRows=27 outputRows=27 (min=1, max=1) InputSize=1.05 KiB OutputSize=432 bytes ReadSize=0 bytes|0 bytes|0 bytes MemorySize=1.50 KiB (min=16 bytes, max=40 bytes)
Index Reader Param: Sort Key: l2_distance_sq(__mo_index_centroid_fk_entry, ) INTERNAL Limit: 1
Filter Cond: prefix_in(__mo_cpkey_col)
Block Filter Cond: prefix_in(__mo_cpkey_col)

[cpegeric]

when running concurrent 64 threads test, the execution plan will dop = 64 is very slow. when hardcoded dop = 4, QPS increase from 1 to 12. I saw we have "max_dop" variable, but it doesn't use it.

[cpegeric]

#23520 is related.

[iamlinjunhong]

并发时，每条sql占用所有cpu问题