search

matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.79k stars 276 forks source link

[Subtask]: Vector Index Benchmark + Optimization #14610

Closed arjunsk closed 7 months ago

arjunsk commented 9 months ago

Parent Issue

https://github.com/matrixorigin/matrixone/issues/10634

Detail of Subtask

Describe implementation you've considered

No response

Additional information

No response

arjunsk commented 9 months ago

This is the details so far: https://github.com/matrixorigin/docs/pull/269/files#diff-5c29fcdf39df9a922e45f536800d5b3fb8cd31ac65fdf1080f9368cd71411ecd

arjunsk commented 8 months ago

Things Tried out

  1. Remove unnecessary projections
  2. Replace Gonums with Simple Logic to avoid conversion overhead for NormalizeL2 and L2Distance function
  3. Use INDEX Join instead of INNER Join for joining tbl with index table join.
  4. For memory allocation in NormalizeL2 use sync.Pool for allocating temporary output array.
  5. Testing it with a table having Primary Key

Pending

  1. Test benchmark again on pure SQL-only query.

Problems

  1. QPS did not change much between with index and without index.
  2. Change in Precision points. (Not a big problem)

Benchmark

Old QPS New QPS
Without Index without PK 1 11.97
With Index without PK 4 12.62
Without Index with PK NA 1.9
With Index with PK NA 23.39

Mo With Index

mysql> explain analyze SELECT a FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=47ms inputRows=100 outputRows=100 InputSize=400bytes OutputSize=400bytes MemorySize=400bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         Analyze: timeConsumed=3ms waitTime=94ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=400bytes MemorySize=23568bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|         Sort Key: l2_distance(t3.b, cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                          |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               Analyze: timeConsumed=0ms waitTime=530ms inputRows=3592 outputRows=1796 InputSize=977024bytes OutputSize=969840bytes MemorySize=0bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               Join Type: INDEX                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Join Cond: (t3.a = __mo_index_secondary_018e7992-4272-7901-9f21-92e187264b55.__mo_index_pri_col)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|               ->  Table Scan on a.t3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     Analyze: timeConsumed=206ms scan_time=[total=206ms,min=8ms,max=33ms,dop=10] filter_time=[total=0ms,min=0ms,max=0ms,dop=10] waitTime=33ms inputRows=1796 outputRows=1796 InputSize=969840bytes OutputSize=969840bytes MemorySize=1033196bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|                     Runtime Filter Probe: t3.a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Analyze: timeConsumed=7ms probe_time=[total=7ms,min=0ms,max=0ms,dop=10] build_time=[0ms] waitTime=121ms inputRows=1000001 outputRows=1796 InputSize=11mb OutputSize=7184bytes MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|                     Join Type: INNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     Join Cond: (__mo_index_secondary_018e7992-4272-7901-9f21-92e187264b55.__mo_index_centroid_fk_id = __mo_index_secondary_018e7992-4272-7d67-9ea5-771619ffa993.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=11ms probe_time=[total=11ms,min=0ms,max=1ms,dop=10] build_time=[0ms] waitTime=113ms inputRows=1000001 outputRows=1000000 InputSize=19mb OutputSize=11mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                           Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                           Join Cond: (__mo_index_secondary_018e7992-4272-7901-9f21-92e187264b55.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                           ->  Table Scan on a.__mo_index_secondary_018e7992-4272-7901-9f21-92e187264b55                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Analyze: timeConsumed=78ms scan_time=[total=78ms,min=8ms,max=10ms,dop=8] waitTime=8ms inputRows=1000000 outputRows=1000000 InputSize=19mb OutputSize=19mb MemorySize=2mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|                           ->  Table Scan on a.__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Filter Cond: (__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                     ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4016bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Sort Key: l2_distance(__mo_index_secondary_018e7992-4272-7d67-9ea5-771619ffa993.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                           Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=500 InputSize=24bytes OutputSize=272000bytes MemorySize=32906bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|                                 Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                                 Join Cond: (__mo_index_secondary_018e7992-4272-7d67-9ea5-771619ffa993.__mo_index_centroid_version = cast(__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7992-4272-7d67-9ea5-771619ffa993                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Filter Cond: (__mo_index_secondary_018e7992-4272-7711-a386-bcb4acce2407.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
40 rows in set (0.05 sec)

Mo Without Index

mysql> explain analyze SELECT a FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   Analyze: timeConsumed=0ms waitTime=105ms inputRows=100 outputRows=100 InputSize=400bytes OutputSize=400bytes MemorySize=400bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|         Analyze: timeConsumed=192ms waitTime=210ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=400bytes MemorySize=7mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         Sort Key: l2_distance(t3.b, cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|         ->  Table Scan on a.t3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               Analyze: timeConsumed=664ms scan_time=[total=664ms,min=62ms,max=86ms,dop=9] waitTime=0ms inputRows=1000000 outputRows=1000000 InputSize=514mb OutputSize=514mb MemorySize=75mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.11 sec)

PG With Index

QUERY PLAN                                                                                                                                                                                                                                                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=350.00..351.29 rows=100 width=12)                                                                                                                                                                                                                 |
  ->  Index Scan using idx3 on t3  (cost=350.00..13262.00 rows=1000000 width=12)                                                                                                                                                                               |
        Order By: (b <=> '[1,3,11,110,62,22,4,0,43,21,22,18,6,28,64,9,11,1,0,0,1,40,101,21,20,2,4,2,2,9,18,35,1,1,7,25,108,116,63,2,0,0,11,74,40,101,116,3,33,1,1,11,14,18,116,116,68,12,5,4,2,2,9,102,17,3,10,18,8,15,67,63,15,0,14,116,80,0,2,22,96,37,28,88,|

PG Without Index

QUERY PLAN                                                                                                                                                                                                                                                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=94603.74..94615.41 rows=100 width=12)                                                                                                                                                                                                             |
  ->  Gather Merge  (cost=94603.74..191832.83 rows=833334 width=12)                                                                                                                                                                                            |
        Workers Planned: 2                                                                                                                                                                                                                                     |
        ->  Sort  (cost=93603.71..94645.38 rows=416667 width=12)                                                                                                                                                                                               |
              Sort Key: ((b <=> '[1,3,11,110,62,22,4,0,43,21,22,18,6,28,64,9,11,1,0,0,1,40,101,21,20,2,4,2,2,9,18,35,1,1,7,25,108,116,63,2,0,0,11,74,40,101,116,3,33,1,1,11,14,18,116,116,68,12,5,4,2,2,9,102,17,3,10,18,8,15,67,63,15,0,14,116,80,0,2,22,96,37|
              ->  Parallel Seq Scan on t3  (cost=0.00..77679.00 rows=416667 width=12)                                                                                                                                                                          |
arjunsk commented 8 months ago

Setup for Mo

  1. Run the branch: https://github.com/arjunsk/matrixone/tree/bug/perf_vec_index2
  2. Download the SQL import file from S3.
  3. Import using below query 
    source /Users/arjunsunilkumar/GolandProjects/mo_dump/sift128_no_index.sql
  4. Once the table is imported, run 
    
-- takes about <5secs in the master. 
-- experimenting: if with the PK column defined, we have any fast path for table scans.
ALTER TABLE t3 ADD PRIMARY KEY (a);

mysql> create index idx3 using ivfflat on t3(b) lists=500 op_type "vector_l2_ops"; Query OK, 0 rows affected (6 min 8.79 sec)

4. Run the query
```sql
explain analyze SELECT a FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;

Setting Benchmark Code

  1. Download Archive.zip

  2. Run benchmark code. Replace /Users/arjunsunilkumar/Downloads/benchmark/1million128/sift/ with the path where you extracted the above dataset.

Read More ```python import time from sqlalchemy import create_engine, text import numpy as np import struct def read_fvecs_file(filename, start=1, end=-1): vectors = [] with open(filename, 'rb') as f: current_index = 1 while True: bytes_read = f.read(4) if not bytes_read: break d, = struct.unpack('i', bytes_read) if start <= current_index <= end or (current_index >= start and end == -1): vec = np.fromfile(f, dtype=np.float32, count=d) vectors.append(vec) else: f.seek(d * 4, 1) if end != -1 and current_index >= end: break current_index += 1 return vectors def read_ivecs_file(filename): with open(filename, 'rb') as f: vectors = [] while True: bytes = f.read(4) if not bytes: break dim = struct.unpack('i', bytes)[0] vector = np.fromfile(f, dtype=np.int32, count=dim) vectors.append(vector) return vectors def execute_knn_query(select_query, conn): result = conn.execute(text(select_query)) return [id for id, in result.fetchall()] def build_knn_query_template_with_ivfflat(input_vector_val, options): org_tbl_name = options['OrgTblName'] org_tbl_id_name = options['OrgTblIdName'] org_tbl_sk_name = options['OrgTblSkName'] k = options['K'] input_vector_str = '[' + ','.join(map(str, input_vector_val)) + ']' if options['DBType'] == 'mysql': sel_qry = f"SELECT {org_tbl_id_name} FROM {org_tbl_name} ORDER BY l2_distance({org_tbl_sk_name},'{input_vector_str}') ASC LIMIT {k};" else: sel_qry = f"SELECT {org_tbl_id_name}-1 FROM {org_tbl_name} ORDER BY {org_tbl_sk_name}<->'{input_vector_str}' ASC LIMIT {k};" return sel_qry def exec_set_params(conn): probe_val = options['ProbeVal'] if options['DBType'] == 'mysql': set_qry = f"SET @probe_limit={probe_val};" else: set_qry = f"SET ivfflat.probes={probe_val};" conn.execute(text(set_qry)) def calc_recall(count: int, ground_truth: list[np.ndarray], got: list[int]) -> float: ground_truth_set = set(np.concatenate(ground_truth)) match = np.zeros(count) for idx, result in enumerate(got[:count]): if result in ground_truth_set: match[idx] = 1 return np.mean(match) if __name__ == "__main__": query_vectors = read_fvecs_file('/Users/arjunsunilkumar/Downloads/benchmark/1million128/sift/sift_query.fvecs') expected_results = read_ivecs_file( '/Users/arjunsunilkumar/Downloads/benchmark/1million128/sift/sift_groundtruth.ivecs') actual_results = [] options = { # "DBType": "postgres", # "DbName": "postgres", "DBType": "mysql", "DbName": "a", "OrgTblName": "t3", "OrgTblIdName": "a", "OrgTblSkName": "b", "ProbeVal": 5, "K": 100, } if options["DBType"] == "mysql": engine = create_engine("mysql+mysqldb://root:111@127.0.0.1:6001/" + options["DbName"]) else: engine = create_engine("postgresql+psycopg2://postgres:111@127.0.0.1:5432/" + options["DbName"]) latencies, recalls = [], [] count = 0 with engine.connect() as conn: exec_set_params(conn) for i, vec in enumerate(query_vectors): count += 1 select_query = build_knn_query_template_with_ivfflat(vec, options) start_time = time.perf_counter() # print(select_query) # break actual_result = execute_knn_query(select_query, conn) duration = time.perf_counter() - start_time latencies.append(duration) actual_results.append(actual_result) recall = calc_recall(options["K"], [expected_results[i].astype(np.float32)], actual_result) print(f"Query {i + 1} completed in {duration:.4f}s" + f" with recall: {recall:.4f}") # print(f"Actual Result: {actual_result}") # print(f"Expected Result: {expected_results[i].tolist()}") # break recalls.append(recall) if i == 100: break avg_latency = round(np.mean(latencies), 4) avg_recall = round(np.mean(recalls), 4) total_duration = round(np.sum(latencies), 4) qps = round(count / total_duration, 4) print( f"Recall: {avg_recall:.4f}, Total Duration: {total_duration:.4f}s, Avg Latency: {avg_latency:.4f}, QPS: {qps:.4f}") ```
arjunsk commented 8 months ago

Benchmark

Old QPS New QPS
Without Index + without PK 1 11.97
With Index + without PK 4 12.62
Without Index + with PK NA 1.9
With Index + with PK NA 23.39
Vector In Index Table + With PK + Only Index Tables NA 7.1046
Vector In Index Table + With PK + tbl INDEX_JOIN entries NA 6.8001

Change 1 - Add Vector column to Entries table and only use Index tables for output

mysql> explain analyze
    -> SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   Analyze: timeConsumed=0ms waitTime=164ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         Analyze: timeConsumed=5ms waitTime=327ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=95344bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|         Sort Key: l2_distance(#[0,1], cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                  |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Analyze: timeConsumed=11ms probe_time=[total=11ms,min=0ms,max=1ms,dop=10] build_time=[0ms] waitTime=1780ms inputRows=1000001 outputRows=4068 InputSize=522mb OutputSize=2mb MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Join Type: INNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|               Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82.__mo_index_centroid_fk_id = __mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=103ms probe_time=[total=102ms,min=7ms,max=13ms,dop=10] build_time=[0ms] waitTime=1852ms inputRows=1000001 outputRows=1000000 InputSize=530mb OutputSize=522mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                     Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                     ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=1139ms scan_time=[total=1138ms,min=113ms,max=162ms,dop=8] waitTime=158ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Filter Cond: (__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Sort Key: l2_distance(__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                     Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid_version = cast(__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Filter Cond: (__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32 rows in set (0.17 sec)

Change 2 - Add Vector column to Entries table and do INDEX_JOIN on the top

To switch you need update this value to false: https://github.com/arjunsk/matrixone/pull/85#discussion_r1540293402

mysql> explain analyze SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=198ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|   ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         Analyze: timeConsumed=0ms waitTime=2701ms inputRows=200 outputRows=100 InputSize=54400bytes OutputSize=54000bytes MemorySize=0bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|         Join Type: INDEX                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         Join Cond: (t3.a = __mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82.__mo_index_pri_col)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|         ->  Table Scan on a.t3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Analyze: timeConsumed=237ms scan_time=[total=237ms,min=15ms,max=30ms,dop=10] filter_time=[total=0ms,min=0ms,max=0ms,dop=10] waitTime=35ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=66520bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               Runtime Filter Probe: t3.a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               Analyze: timeConsumed=4ms waitTime=323ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=400bytes MemorySize=41744bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|               Sort Key: l2_distance(#[0,1], cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                  |
|               Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Analyze: timeConsumed=10ms probe_time=[total=10ms,min=0ms,max=1ms,dop=10] build_time=[0ms] waitTime=1761ms inputRows=1000001 outputRows=4068 InputSize=522mb OutputSize=2mb MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Join Type: INNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82.__mo_index_centroid_fk_id = __mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=81ms probe_time=[total=81ms,min=4ms,max=19ms,dop=10] build_time=[0ms] waitTime=1847ms inputRows=1000001 outputRows=1000000 InputSize=530mb OutputSize=522mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                           Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                           ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7f37-8704-2b07ff96ae82                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Analyze: timeConsumed=1065ms scan_time=[total=1064ms,min=101ms,max=158ms,dop=8] waitTime=102ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                           ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 Filter Cond: (__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                     ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Sort Key: l2_distance(__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                           Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                                 Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                                 Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                                 Join Cond: (__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263.__mo_index_centroid_version = cast(__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7a91-9277-0e0558bae263                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Filter Cond: (__mo_index_secondary_018e7c4d-f1e3-7e42-9ed1-904d573a226c.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
40 rows in set (0.21 sec)
arjunsk commented 8 months ago

Branch

https://github.com/arjunsk/matrixone/pull/85/files

SQL-only based plan

explain analyze
select `__mo_index_pri_col`, `__mo_index_centroid_fk_entry` from `__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f`
where `__mo_index_centroid_fk_version`=(select CAST(`__mo_index_val` as BIGINT) from `__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e` where `__mo_index_key` = 'version') and `__mo_index_centroid_fk_id`
in (
    select `__mo_index_centroid_id` from `__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad`
    where `__mo_index_centroid_version`= (select CAST(`__mo_index_val` as BIGINT) from `__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e` where `__mo_index_key` = 'version')
    order by l2_distance(`__mo_index_centroid`, normalize_l2('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]')) asc  limit 1
   )
order by l2_distance(`__mo_index_centroid_fk_entry`, '[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') asc  limit 100;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   Analyze: timeConsumed=0ms waitTime=189ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         Analyze: timeConsumed=5ms waitTime=376ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=71152bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|         Sort Key: l2_distance(__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_entry, cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                  |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Analyze: timeConsumed=10ms probe_time=[total=10ms,min=0ms,max=2ms,dop=10] build_time=[0ms] waitTime=2058ms inputRows=1000001 outputRows=1057 InputSize=522mb OutputSize=570780bytes MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_id = __mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               ->  Filter                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     Analyze: timeConsumed=28ms waitTime=0ms inputRows=1000000 outputRows=1000000 InputSize=553mb OutputSize=522mb MemorySize=43mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                     Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Analyze: timeConsumed=282ms probe_time=[total=281ms,min=20ms,max=51ms,dop=10] build_time=[0ms] waitTime=1917ms inputRows=1000001 outputRows=1000000 InputSize=530mb OutputSize=553mb MemorySize=24bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=1347ms scan_time=[total=1347ms,min=144ms,max=184ms,dop=8] waitTime=170ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|                                 Block Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=0ms waitTime=5ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Sort Key: l2_distance(__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                     Limit: 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                     ->  Filter                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=288000bytes OutputSize=272000bytes MemorySize=272500bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|                                 Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=6ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=288000bytes MemorySize=24bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|                                 Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                                       Analyze: timeConsumed=2ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                                       Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|                                       Block Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
38 rows in set (0.19 sec)

Optimizer Plan 1 (SEMI join + SINGLE join)

mysql> explain analyze
    -> SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   Analyze: timeConsumed=0ms waitTime=169ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         Analyze: timeConsumed=3ms waitTime=337ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=70800bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|         Sort Key: l2_distance(#[0,1], cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                  |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Analyze: timeConsumed=10ms probe_time=[total=9ms,min=0ms,max=1ms,dop=10] build_time=[0ms] waitTime=1840ms inputRows=1000001 outputRows=1057 InputSize=522mb OutputSize=570780bytes MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_id = __mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=197ms probe_time=[total=197ms,min=14ms,max=27ms,dop=10] build_time=[0ms] waitTime=1807ms inputRows=1000001 outputRows=1000000 InputSize=530mb OutputSize=522mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|                     Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                     Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                     ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=1222ms scan_time=[total=1222ms,min=139ms,max=165ms,dop=8] waitTime=160ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|                     ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Sort Key: l2_distance(__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                     Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32 rows in set (0.19 sec)

Optimizer Plan 2 - SEMI join's only

mysql> explain analyze
    -> SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    3326
Current database: a

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   Analyze: timeConsumed=0ms waitTime=145ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         Analyze: timeConsumed=3ms waitTime=289ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=70952bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|         Sort Key: l2_distance(#[0,1], cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                  |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Analyze: timeConsumed=12ms probe_time=[total=11ms,min=0ms,max=3ms,dop=10] build_time=[0ms] waitTime=1574ms inputRows=1000001 outputRows=1057 InputSize=522mb OutputSize=570780bytes MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_id = __mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=388ms probe_time=[total=388ms,min=27ms,max=48ms,dop=10] build_time=[0ms] waitTime=1323ms inputRows=1000001 outputRows=1000000 InputSize=530mb OutputSize=522mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|                     Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|                     Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                     ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7881-9aae-f6cbef94d09f                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=965ms scan_time=[total=965ms,min=101ms,max=139ms,dop=8] waitTime=111ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                     ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                           Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=0ms waitTime=2ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Sort Key: l2_distance(__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                     Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|                           Join Cond: (__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad.__mo_index_centroid_version = cast(__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7720-bed9-8bfaa84588ad                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Filter Cond: (__mo_index_secondary_018e7ca4-1f5b-7903-bdec-44c2ff16a66e.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32 rows in set (0.17 sec)

Heap Profile

heap.pprof.zip

arjunsk commented 8 months ago

Benchmark

QPS: 6.78

Query Plan

explain analyze
SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=250ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         Analyze: timeConsumed=1ms waitTime=499ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=82648bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         Sort Key: l2_distance(#[0,1], cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                                                                                                        |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               Analyze: timeConsumed=1ms probe_time=[total=1ms,min=0ms,max=1ms,dop=10] build_time=[0ms] waitTime=2744ms inputRows=3282 outputRows=3281 InputSize=1mb OutputSize=1mb MemorySize=180875bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               Join Cond: (__mo_index_secondary_018e7e97-aaa2-7a92-ae7a-f5c186ff627b.__mo_index_centroid_fk_version = cast(__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Analyze: timeConsumed=12ms probe_time=[total=12ms,min=0ms,max=2ms,dop=10] build_time=[0ms] waitTime=2981ms inputRows=1000001 outputRows=3281 InputSize=530mb OutputSize=1mb MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                     Join Cond: (__mo_index_secondary_018e7e97-aaa2-7a92-ae7a-f5c186ff627b.__mo_index_centroid_fk_id = __mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                     ->  Table Scan on a.__mo_index_secondary_018e7e97-aaa2-7a92-ae7a-f5c186ff627b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Analyze: timeConsumed=1904ms scan_time=[total=1904ms,min=219ms,max=249ms,dop=8] waitTime=238ms inputRows=1000000 outputRows=1000000 InputSize=530mb OutputSize=530mb MemorySize=69mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4016bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Sort Key: l2_distance(__mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                           Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=500 InputSize=24bytes OutputSize=272000bytes MemorySize=32906bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|                                 Join Type: SINGLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                                 Join Cond: (__mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454.__mo_index_centroid_version = cast(__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Filter Cond: (__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|               ->  Table Scan on a.__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                     Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                     Filter Cond: (__mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32 rows in set (0.26 sec)

Hidden Table Defenition

mysql> select * from mo_catalog.mo_indexes where name="idx3";
+------+----------+-------------+------+----------+---------+-----------------+-------------------------------------------+------------+--------+---------+-------------+------------------+---------+-----------------------------------------------------------+
| id   | table_id | database_id | name | type     | algo    | algo_table_type | algo_params                               | is_visible | hidden | comment | column_name | ordinal_position | options | index_table_name                                          |
+------+----------+-------------+------+----------+---------+-----------------+-------------------------------------------+------------+--------+---------+-------------+------------------+---------+-----------------------------------------------------------+
|   63 |   272519 |      272518 | idx3 | MULTIPLE | ivfflat | metadata        | {"lists":"500","op_type":"vector_l2_ops"} |          1 |      0 |         | b           |                1 | NULL    | __mo_index_secondary_018e7e97-aaa2-7607-8e60-0732829dab99 |
|   64 |   272519 |      272518 | idx3 | MULTIPLE | ivfflat | centroids       | {"lists":"500","op_type":"vector_l2_ops"} |          1 |      0 |         | b           |                1 | NULL    | __mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454 |
|   65 |   272519 |      272518 | idx3 | MULTIPLE | ivfflat | entries         | {"lists":"500","op_type":"vector_l2_ops"} |          1 |      0 |         | b           |                1 | NULL    | __mo_index_secondary_018e7e97-aaa2-7a92-ae7a-f5c186ff627b |
+------+----------+-------------+------+----------+---------+-----------------+-------------------------------------------+------------+--------+---------+-------------+------------------+---------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> desc `__mo_index_secondary_018e7e97-aaa2-7b63-8330-7d6ca43d0454`;
+-----------------------------+-------------+------+------+---------+-------+---------+
| Field                       | Type        | Null | Key  | Default | Extra | Comment |
+-----------------------------+-------------+------+------+---------+-------+---------+
| __mo_index_centroid_version | BIGINT(64)  | NO   | PRI  | NULL    |       |         |
| __mo_index_centroid_id      | BIGINT(64)  | NO   | PRI  | NULL    |       |         |
| __mo_index_centroid         | VECF32(128) | YES  |      | NULL    |       |         |
+-----------------------------+-------------+------+------+---------+-------+---------+
3 rows in set (0.01 sec)

mysql> desc `__mo_index_secondary_018e7e97-aaa2-7a92-ae7a-f5c186ff627b`;
+--------------------------------+-------------+------+------+---------+-------+---------+
| Field                          | Type        | Null | Key  | Default | Extra | Comment |
+--------------------------------+-------------+------+------+---------+-------+---------+
| __mo_index_centroid_fk_version | BIGINT(64)  | NO   | PRI  | NULL    |       |         |
| __mo_index_centroid_fk_id      | BIGINT(64)  | NO   | PRI  | NULL    |       |         |
| __mo_index_pri_col             | INT(32)     | NO   | PRI  | NULL    |       |         |
| __mo_index_centroid_fk_entry   | VECF32(128) | YES  |      | NULL    |       |         |
+--------------------------------+-------------+------+------+---------+-------+---------+
4 rows in set (0.01 sec)
arjunsk commented 8 months ago
  1. Run the branch: https://github.com/arjunsk/matrixone/tree/bug/add-vector-to-entries-table
  2. Download the SQL import file from S3.
  3. Import using below query 
    source /Users/arjunsunilkumar/GolandProjects/mo_dump/sift128_no_index.sql
  4. Once the table is imported, run 
    mysql> create index idx3 using ivfflat on t3(b) lists=500 op_type "vector_l2_ops";
Query OK, 0 rows affected (6 min 8.79 sec)
  5. Run the query 
    explain analyze SELECT a FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
arjunsk commented 8 months ago

Waiting for this issue: https://github.com/matrixorigin/matrixone/issues/15196

arjunsk commented 7 months ago

Using INDEX JOIN to join TableScan and SortNode gives the wrong output.

drop table vector_index_01;
create table vector_index_01(a int primary key, b vecf32(128),c int,key c_k(c));
insert into vector_index_01 values(9774 ,"[1, 0, 1, 6, 6, 17, 47, 39, 2, 0, 1, 25, 27, 10, 56, 130, 18, 5, 2, 6, 15, 2, 19, 130, 42, 28, 1, 1, 2, 1, 0, 5, 0, 2, 4, 4, 31, 34, 44, 35, 9, 3, 8, 11, 33, 12, 61, 130, 130, 17, 0, 1, 6, 2, 9, 130, 111, 36, 0, 0, 11, 9, 1, 12, 2, 100, 130, 28, 7, 2, 6, 7, 9, 27, 130, 83, 5, 0, 1, 18, 130, 130, 84, 9, 0, 0, 2, 24, 111, 24, 0, 1, 37, 24, 2, 10, 12, 62, 33, 3, 0, 0, 0, 1, 3, 16, 106, 28, 0, 0, 0, 0, 17, 46, 85, 10, 0, 0, 1, 4, 11, 4, 2, 2, 9, 14, 8, 8]",3),(9775,"[0, 1, 1, 3, 0, 3, 46, 20, 1, 4, 17, 9, 1, 17, 108, 15, 0, 3, 37, 17, 6, 15, 116, 16, 6, 1, 4, 7, 7, 7, 9, 6, 0, 8, 10, 4, 26, 129, 27, 9, 0, 0, 5, 2, 11, 129, 129, 12, 103, 4, 0, 0, 2, 31, 129, 129, 94, 4, 0, 0, 0, 3, 13, 42, 0, 15, 38, 2, 70, 129, 1, 0, 5, 10, 40, 12, 74, 129, 6, 1, 129, 39, 6, 1, 2, 22, 9, 33, 122, 13, 0, 0, 0, 0, 5, 23, 4, 11, 9, 12, 45, 38, 1, 0, 0, 4, 36, 38, 57, 32, 0, 0, 82, 22, 9, 5, 13, 11, 3, 94, 35, 3, 0, 0, 0, 1, 16, 97]",5),(9776,"[10, 3, 8, 5, 48, 26, 5, 16, 17, 0, 0, 2, 132, 53, 1, 16, 112, 6, 0, 0, 7, 2, 1, 48, 48, 15, 18, 31, 3, 0, 0, 9, 6, 10, 19, 27, 50, 46, 17, 9, 18, 1, 4, 48, 132, 23, 3, 5, 132, 9, 4, 3, 11, 0, 2, 46, 84, 12, 10, 10, 1, 0, 12, 76, 26, 22, 16, 26, 35, 15, 3, 16, 15, 1, 51, 132, 125, 8, 1, 2, 132, 51, 67, 91, 8, 0, 0, 30, 126, 39, 32, 38, 4, 0, 1, 12, 24, 2, 2, 2, 4, 7, 2, 19, 93, 19, 70, 92, 2, 3, 1, 21, 36, 58, 132, 94, 0, 0, 0, 0, 21, 25, 57, 48, 1, 0, 0, 1]",3);
insert into vector_index_01 values(9777, " [16, 15, 0, 0, 5, 46, 5, 5, 4, 0, 0, 0, 28, 118, 12, 5, 75, 44, 5, 0, 6, 32, 6, 49, 41, 74, 9, 1, 0, 0, 0, 9, 1, 9, 16, 41, 71, 80, 3, 0, 0, 4, 3, 5, 51, 106, 11, 3, 112, 28, 13, 1, 4, 8, 3, 104, 118, 14, 1, 1, 0, 0, 0, 88, 3, 27, 46, 118, 108, 49, 2, 0, 1, 46, 118, 118, 27, 12, 0, 0, 33, 118, 118, 8, 0, 0, 0, 4, 118, 95, 40, 0, 0, 0, 1, 11, 27, 38, 12, 12, 18, 29, 3, 2, 13, 30, 94, 78, 30, 19, 9, 3, 31, 45, 70, 42, 15, 1, 3, 12, 14, 22, 16, 2, 3, 17, 24, 13]",4),(9778,"[41, 0, 0, 7, 1, 1, 20, 67, 9, 0, 0, 0, 0, 31, 120, 61, 25, 0, 0, 0, 0, 10, 120, 90, 32, 0, 0, 1, 13, 11, 22, 50, 4, 0, 2, 93, 40, 15, 37, 18, 12, 2, 2, 19, 8, 44, 120, 25, 120, 5, 0, 0, 0, 2, 48, 97, 102, 14, 3, 3, 11, 9, 34, 41, 0, 0, 4, 120, 56, 3, 4, 5, 6, 15, 37, 116, 28, 0, 0, 3, 120, 120, 24, 6, 2, 0, 1, 28, 53, 90, 51, 11, 11, 2, 12, 14, 8, 6, 4, 30, 9, 1, 4, 22, 25, 79, 120, 66, 5, 0, 0, 6, 42, 120, 91, 43, 15, 2, 4, 39, 12, 9, 9, 12, 15, 5, 24, 36]",4);
select a from vector_index_01 order by  L2_DISTANCE(b, "[16, 15, 0, 0, 5, 46, 5, 5, 4, 0, 0, 0, 28, 118, 12, 5, 75, 44, 5, 0, 6, 32, 6, 49, 41, 74, 9, 1, 0, 0, 0, 9, 1, 9, 16, 41, 71, 80, 3, 0, 0, 4, 3, 5, 51, 106, 11, 3, 112, 28, 13, 1, 4, 8, 3, 104, 118, 14, 1, 1, 0, 0, 0, 88, 3, 27, 46, 118, 108, 49, 2, 0, 1, 46, 118, 118, 27, 12, 0, 0, 33, 118, 118, 8, 0, 0, 0, 4, 118, 95, 40, 0, 0, 0, 1, 11, 27, 38, 12, 12, 18, 29, 3, 2, 13, 30, 94, 78, 30, 19, 9, 3, 31, 45, 70, 42, 15, 1, 3, 12, 14, 22, 16, 2, 3, 17, 24, 13]") ASC LIMIT 2;

+------+
| a    |
+------+
| 9777 |
| 9776 |
+------+
2 rows in set (0.00 sec)

+------+
| a    |
+------+
| 9774 |
| 9775 |
+------+
2 rows in set (0.01 sec)
arjunsk commented 7 months ago 
mysql> 
mysql> explain analyze
    -> SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    4052
Current database: a

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   Analyze: timeConsumed=0ms waitTime=207ms inputRows=100 outputRows=100 InputSize=253168bytes OutputSize=253168bytes MemorySize=253168bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|         Analyze: timeConsumed=0ms waitTime=2384ms inputRows=11180 outputRows=100 InputSize=2mb OutputSize=253168bytes MemorySize=0bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|         Join Type: INDEX                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         Join Cond: (t3.a = __mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_pri_col)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|         ->  Table Scan on a.t3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|               Analyze: timeConsumed=1465ms scan_time=[total=1463ms,min=116ms,max=198ms,dop=9] filter_time=[total=1ms,min=0ms,max=0ms,dop=9] waitTime=198ms inputRows=5590 outputRows=5590 InputSize=2mb OutputSize=2mb MemorySize=3mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|               Runtime Filter Probe: t3.a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|               Analyze: timeConsumed=2ms sort_time=[total=2ms,min=0ms,max=2ms,dop=10] mergesort_time=[0ms] waitTime=7ms inputRows=0 outputRows=5590 InputSize=0bytes OutputSize=22360bytes MemorySize=22360bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               Sort Key: l2_distance(__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_entry, cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                  |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|                     Analyze: timeConsumed=3ms probe_time=[total=3ms,min=0ms,max=3ms,dop=10] build_time=[0ms] waitTime=30ms inputRows=5591 outputRows=5590 InputSize=4mb OutputSize=2mb MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                     Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                     Join Cond: (__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_id = __mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|                     Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Analyze: timeConsumed=1ms waitTime=0ms inputRows=8192 outputRows=5590 InputSize=4mb OutputSize=4mb MemorySize=8mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                           Runtime Filter Probe: __mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                     ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                           Sort Key: l2_distance(__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                           Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                                 Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                                 Join Type: INNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|                                 Join Cond: (__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid_version = cast(__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                       Filter Cond: (__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
35 rows in set (0.22 sec)
arjunsk commented 7 months ago

Plan Without INDEX JOIN


mysql> 
mysql> explain analyze
    -> SELECT a,b FROM t3 ORDER BY l2_distance(b,'[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]') ASC LIMIT 100;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    4208
Current database: a

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   Analyze: timeConsumed=0ms waitTime=6ms inputRows=100 outputRows=100 InputSize=54000bytes OutputSize=54000bytes MemorySize=54000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         Analyze: timeConsumed=1ms waitTime=12ms inputRows=0 outputRows=100 InputSize=0bytes OutputSize=54000bytes MemorySize=100320bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|         Sort Key: l2_distance(__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_entry, cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32)) ASC                  |
|         Limit: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               Analyze: timeConsumed=3ms probe_time=[total=3ms,min=0ms,max=3ms,dop=10] build_time=[0ms] waitTime=20ms inputRows=5591 outputRows=5590 InputSize=4mb OutputSize=2mb MemorySize=180859bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|               Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               Join Cond: (__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_id = __mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|               Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|               ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Analyze: timeConsumed=1ms waitTime=0ms inputRows=8192 outputRows=5590 InputSize=4mb OutputSize=4mb MemorySize=8mb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                     Runtime Filter Probe: __mo_index_secondary_018e9d5b-bd98-7e91-86ac-c2ac887c8c99.__mo_index_centroid_fk_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|               ->  Sort                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                     Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=4024bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|                     Sort Key: l2_distance(__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid, normalize_l2(cast('[1.0,3.0,11.0,110.0,62.0,22.0,4.0,0.0,43.0,21.0,22.0,18.0,6.0,28.0,64.0,9.0,11.0,1.0,0.0,0.0,1.0,40.0,101.0,21.0,20.0,2.0,4.0,2.0,2.0,9.0,18.0,35.0,1.0,1.0,7.0,25.0,108.0,116.0,63.0,2.0,0.0,0.0,11.0,74.0,40.0,101.0,116.0,3.0,33.0,1.0,1.0,11.0,14.0,18.0,116.0,116.0,68.0,12.0,5.0,4.0,2.0,2.0,9.0,102.0,17.0,3.0,10.0,18.0,8.0,15.0,67.0,63.0,15.0,0.0,14.0,116.0,80.0,0.0,2.0,22.0,96.0,37.0,28.0,88.0,43.0,1.0,4.0,18.0,116.0,51.0,5.0,11.0,32.0,14.0,8.0,23.0,44.0,17.0,12.0,9.0,0.0,0.0,19.0,37.0,85.0,18.0,16.0,104.0,22.0,6.0,2.0,26.0,12.0,58.0,67.0,82.0,25.0,12.0,2.0,2.0,25.0,18.0,8.0,2.0,19.0,42.0,48.0,11.0]' AS VECF32))) ASC |
|                     Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|                     ->  Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|                           Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=501 outputRows=500 InputSize=276024bytes OutputSize=272000bytes MemorySize=49347bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                           Join Type: INNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|                           Join Cond: (__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1.__mo_index_centroid_version = cast(__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075.__mo_index_val AS BIGINT))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7de9-a664-f6fe37bb1de1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=500 outputRows=500 InputSize=276000bytes OutputSize=276000bytes MemorySize=552000bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                           ->  Table Scan on a.__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|                                 Filter Cond: (__mo_index_secondary_018e9d5b-bd98-7a25-bca2-ae6966dca075.__mo_index_key = 'version')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
27 rows in set (0.03 sec)
arjunsk commented 7 months ago 

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                                                                                                                                                                                                                                                                                                          |
|   Analyze: timeConsumed=0ms waitTime=1ms inputRows=3 outputRows=3 InputSize=156bytes OutputSize=156bytes MemorySize=156bytes                                                                                                                                                                                                                                                                     |
|   ->  Join                                                                                                                                                                                                                                                                                                                                                                                       |
|         Analyze: timeConsumed=0ms waitTime=15ms inputRows=6 outputRows=3 InputSize=168bytes OutputSize=156bytes MemorySize=0bytes                                                                                                                                                                                                                                                                |
|         Join Type: INDEX                                                                                                                                                                                                                                                                                                                                                                         |
|         Join Cond: (vector_index_04.a = __mo_index_secondary_018ea237-449c-7053-a0d4-5be653091944.__mo_index_pri_col)                                                                                                                                                                                                                                                                            |
|         Runtime Filter Build: #[-1,0]                                                                                                                                                                                                                                                                                                                                                            |
|         Limit: 3                                                                                                                                                                                                                                                                                                                                                                                 |
|         ->  Table Scan on a.vector_index_04                                                                                                                                                                                                                                                                                                                                                      |
|               Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=156bytes OutputSize=156bytes MemorySize=315bytes                                                                                                                                                                                                                                                         |
|               Runtime Filter Probe: vector_index_04.a                                                                                                                                                                                                                                                                                                                                            |
|         ->  Sort                                                                                                                                                                                                                                                                                                                                                                                 |
|               Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=12bytes MemorySize=132bytes                                                                                                                                                                                                                                                            |
|               Sort Key: l2_distance(__mo_index_secondary_018ea237-449c-7053-a0d4-5be653091944.__mo_index_centroid_fk_entry, cast('[9.6,57.22,19.2,78.02]' AS VECF32)) ASC                                                                                                                                                                                                                        |
|               Limit: 3                                                                                                                                                                                                                                                                                                                                                                           |
|               ->  Join                                                                                                                                                                                                                                                                                                                                                                           |
|                     Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=3ms inputRows=10 outputRows=9 InputSize=412bytes OutputSize=252bytes MemorySize=16bytes                                                                                                                                                                                                             |
|                     Join Type: SEMI                                                                                                                                                                                                                                                                                                                                                              |
|                     Join Cond: ((__mo_index_secondary_018ea237-449c-7053-a0d4-5be653091944.__mo_index_centroid_fk_id = __mo_index_secondary_018ea237-449c-76d0-9fc3-dfeb03979e0a.__mo_index_centroid_id) and (__mo_index_secondary_018ea237-449c-7053-a0d4-5be653091944.__mo_index_centroid_fk_version = __mo_index_secondary_018ea237-449c-76d0-9fc3-dfeb03979e0a.__mo_index_centroid_version)) |
|                     ->  Table Scan on a.__mo_index_secondary_018ea237-449c-7053-a0d4-5be653091944                                                                                                                                                                                                                                                                                                |
|                           Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=396bytes OutputSize=396bytes MemorySize=792bytes                                                                                                                                                                                                                                             |
|                     ->  Sort                                                                                                                                                                                                                                                                                                                                                                     |
|                           Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=16bytes MemorySize=40bytes                                                                                                                                                                                                                                                 |
|                           Sort Key: l2_distance(__mo_index_secondary_018ea237-449c-76d0-9fc3-dfeb03979e0a.__mo_index_centroid, normalize_l2(cast('[9.6,57.22,19.2,78.02]' AS VECF32))) ASC                                                                                                                                                                                                       |
|                           Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END                                                                                                                                                                                                                                                                                   |
|                           ->  Join                                                                                                                                                                                                                                                                                                                                                               |
|                                 Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=2 outputRows=1 InputSize=64bytes OutputSize=40bytes MemorySize=49347bytes                                                                                                                                                                                                 |
|                                 Join Type: INNER                                                                                                                                                                                                                                                                                                                                                 |
|                                 Join Cond: (__mo_index_secondary_018ea237-449c-76d0-9fc3-dfeb03979e0a.__mo_index_centroid_version = cast(__mo_index_secondary_018ea237-449c-70ac-aaef-0750c2f6af2d.__mo_index_val AS BIGINT))                                                                                                                                                                    |
|                                 ->  Table Scan on a.__mo_index_secondary_018ea237-449c-76d0-9fc3-dfeb03979e0a                                                                                                                                                                                                                                                                                    |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=40bytes OutputSize=40bytes MemorySize=80bytes                                                                                                                                                                                                                                    |
|                                 ->  Table Scan on a.__mo_index_secondary_018ea237-449c-70ac-aaef-0750c2f6af2d                                                                                                                                                                                                                                                                                    |
|                                       Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes                                                                                                                                                                                                                                    |
|                                       Filter Cond: (__mo_index_secondary_018ea237-449c-70ac-aaef-0750c2f6af2d.__mo_index_key = 'version')                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
34 rows in set (0.03 sec)
arjunsk commented 7 months ago

Expected

+------+
| a    |
+------+
|    9 |
|    8 |
|    5 |
+------+

Actual (only for INDEX JOIN)

+------+
| a    |
+------+
|    5 |
|    8 |
|    9 |
+------+

Table Creation

SQL ```sql drop database a; create database a; use a; create table vector_index_04(a int primary key, b vecf32(3),c vecf32(4)); create index idx01 using ivfflat on vector_index_04(c) lists=5 op_type "vector_l2_ops"; insert into vector_index_04 values(1,"[56,23,6]","[0.25,0.14,0.88,0.0001]"); insert into vector_index_04 values(2,"[77,45,3]","[1.25,5.25,8.699,4.25]"); insert into vector_index_04 values(3,"[8,56,3]","[9.66,5.22,1.22,7.02]"); insert into vector_index_04 values(4,"[156,213,61]","[10.25,0.14,0.88,10.0001]"); insert into vector_index_04 values(5,"[177,425,30]","[11.25,51.25,80.699,44.25]"); insert into vector_index_04 values(6,"[80,56,3]","[90.686,5.212,19.22,7.02]"); insert into vector_index_04 values(7,"[5.6,2.13,6.1]","[1.25,8.14,4.88,18.0001]"); insert into vector_index_04 values(8,"[17.7,4.25,3.0]","[1.215,5.25,8.9,47.25]"); insert into vector_index_04 values(9,"[8,5.6,13]","[9.6,57.22,19.2,78.02]"); ```

Output and Query Plan

KNN With INDEX JOIN [FAIIL] ```sql mysql> select * from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +------+-----------------+-------------------------------+ | a | b | c | +------+-----------------+-------------------------------+ | 5 | [177, 425, 30] | [11.25, 51.25, 80.699, 44.25] | | 8 | [17.7, 4.25, 3] | [1.215, 5.25, 8.9, 47.25] | | 9 | [8, 5.6, 13] | [9.6, 57.22, 19.2, 78.02] | +------+-----------------+-------------------------------+ 3 rows in set (0.00 sec) mysql> mysql> explain analyze select * from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=3 outputRows=3 InputSize=156bytes OutputSize=156bytes MemorySize=156bytes | | -> Join | | Analyze: timeConsumed=0ms waitTime=7ms inputRows=6 outputRows=3 InputSize=168bytes OutputSize=156bytes MemorySize=0bytes | | Join Type: INDEX | | Join Cond: (vector_index_04.a = __mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_pri_col) | | Runtime Filter Build: #[-1,0] | | -> Table Scan on a.vector_index_04 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=156bytes OutputSize=156bytes MemorySize=315bytes | | Runtime Filter Probe: vector_index_04.a | | -> Sort | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=12bytes MemorySize=132bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_entry, cast('[9.6,57.22,19.2,78.02]' AS VECF32)) ASC | | Limit: 3 | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=2ms inputRows=10 outputRows=9 InputSize=412bytes OutputSize=252bytes MemorySize=16bytes | | Join Type: SEMI | | Join Cond: ((__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_id = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_id) and (__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_version = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=396bytes OutputSize=396bytes MemorySize=792bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=16bytes MemorySize=40bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid, normalize_l2(cast('[9.6,57.22,19.2,78.02]' AS VECF32))) ASC | | Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=2 outputRows=1 InputSize=64bytes OutputSize=40bytes MemorySize=49347bytes | | Join Type: INNER | | Join Cond: (__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version = cast(__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_val AS BIGINT)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=40bytes OutputSize=40bytes MemorySize=80bytes | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes | | Filter Cond: (__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_key = 'version') | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 33 rows in set (0.01 sec) ```
KNN using Vector Index Sort Node Alone [Pass] ```sql mysql> select a from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +------+ | a | +------+ | 9 | | 8 | | 5 | +------+ 3 rows in set (0.00 sec) mysql> explain analyze select a from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=12bytes OutputSize=12bytes MemorySize=12bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=12bytes MemorySize=108bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_entry, cast('[9.6,57.22,19.2,78.02]' AS VECF32)) ASC | | Limit: 3 | | -> Join | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=9 InputSize=16bytes OutputSize=252bytes MemorySize=16bytes | | Join Type: SEMI | | Join Cond: ((__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_id = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_id) and (__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_version = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=396bytes OutputSize=396bytes MemorySize=792bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=16bytes MemorySize=32bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid, normalize_l2(cast('[9.6,57.22,19.2,78.02]' AS VECF32))) ASC | | Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END | | -> Join | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=24bytes OutputSize=40bytes MemorySize=32906bytes | | Join Type: INNER | | Join Cond: (__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version = cast(__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_val AS BIGINT)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=40bytes OutputSize=40bytes MemorySize=80bytes | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes | | Filter Cond: (__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_key = 'version') | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.00 sec) ```
KNN With Vector Index INNER JOIN and then Sort [Pass] ```sql mysql> select a from vector_index_04 where a>0 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +------+ | a | +------+ | 9 | | 8 | | 5 | +------+ 3 rows in set (0.00 sec) mysql> mysql> explain analyze select a from vector_index_04 where a>0 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=12bytes OutputSize=12bytes MemorySize=12bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=12bytes MemorySize=108bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_entry, cast('[9.6,57.22,19.2,78.02]' AS VECF32)) ASC | | Limit: 3 | | -> Join | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=9 outputRows=9 InputSize=252bytes OutputSize=252bytes MemorySize=33134bytes | | Join Type: INNER | | Join Cond: (vector_index_04.a = __mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_pri_col) | | -> Table Scan on a.vector_index_04 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=36bytes OutputSize=36bytes MemorySize=81bytes | | Filter Cond: (vector_index_04.a > 0) | | Block Filter Cond: (vector_index_04.a > 0) | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=10 outputRows=9 InputSize=412bytes OutputSize=252bytes MemorySize=16bytes | | Join Type: SEMI | | Join Cond: ((__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_id = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_id) and (__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_version = __mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=396bytes OutputSize=396bytes MemorySize=792bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=16bytes MemorySize=40bytes | | Sort Key: l2_distance(__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid, normalize_l2(cast('[9.6,57.22,19.2,78.02]' AS VECF32))) ASC | | Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=2 outputRows=1 InputSize=64bytes OutputSize=40bytes MemorySize=49347bytes | | Join Type: INNER | | Join Cond: (__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version = cast(__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_val AS BIGINT)) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=40bytes OutputSize=40bytes MemorySize=80bytes | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=48bytes OutputSize=24bytes MemorySize=73bytes | | Filter Cond: (__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_key = 'version') | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 33 rows in set (0.00 sec) ```
KNN Without INDEX [Pass] ```sql mysql> select a, L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") as dist from vector_index_04 order by dist asc limit 3; +------+-------------------+ | a | dist | +------+-------------------+ | 9 | 0 | | 8 | 61.83908166753752 | | 5 | 70.43367683169217 | +------+-------------------+ 3 rows in set (0.01 sec) mysql> mysql> explain analyze select a, L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") as dist from vector_index_04 order by dist asc limit 3; +------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=36bytes OutputSize=36bytes MemorySize=36bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=36bytes MemorySize=36bytes | | Sort Key: l2_distance(vector_index_04.c, [9.6,57.22,19.2,78.02]) ASC | | Limit: 3 | | -> Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=252bytes OutputSize=108bytes MemorySize=36bytes | | -> Table Scan on a.vector_index_04 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=252bytes OutputSize=252bytes MemorySize=504bytes | +------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) ```
Old Vector Index Plan [PASS] ```sql mysql> select * from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +------+-----------------+-------------------------------+ | a | b | c | +------+-----------------+-------------------------------+ | 9 | [8, 5.6, 13] | [9.6, 57.22, 19.2, 78.02] | | 8 | [17.7, 4.25, 3] | [1.215, 5.25, 8.9, 47.25] | | 5 | [177, 425, 30] | [11.25, 51.25, 80.699, 44.25] | +------+-----------------+-------------------------------+ 3 rows in set (0.01 sec) mysql> mysql> explain analyze select * from vector_index_04 order by L2_DISTANCE(c,"[9.6,57.22,19.2,78.02]") asc limit 3; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=156bytes OutputSize=156bytes MemorySize=156bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=1ms inputRows=0 outputRows=3 InputSize=0bytes OutputSize=156bytes MemorySize=276bytes | | Sort Key: l2_distance(vector_index_04.c, cast('[9.6,57.22,19.2,78.02]' AS VECF32)) ASC | | Limit: 3 | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=18 outputRows=9 InputSize=504bytes OutputSize=468bytes MemorySize=49791bytes | | Join Type: INNER hashOnPK | | Join Cond: (#[0,0] = vector_index_04.a) | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=2ms inputRows=10 outputRows=9 InputSize=116bytes OutputSize=36bytes MemorySize=49331bytes | | Join Type: INNER | | Join Cond: (#[0,0] = #[1,0]) | | -> Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=108bytes OutputSize=108bytes MemorySize=108bytes | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=1ms inputRows=10 outputRows=9 InputSize=188bytes OutputSize=108bytes MemorySize=49331bytes | | Join Type: SINGLE | | Join Cond: (__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71.__mo_index_centroid_fk_version = #[1,0]) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7259-93f9-87ef385d0c71 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=180bytes OutputSize=180bytes MemorySize=360bytes | | -> Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=76bytes OutputSize=8bytes MemorySize=0bytes | | -> Filter | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=1 InputSize=196bytes OutputSize=76bytes MemorySize=79bytes | | Filter Cond: (__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_key = 'version') | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=196bytes OutputSize=196bytes MemorySize=392bytes | | -> Sort | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=1 InputSize=0bytes OutputSize=8bytes MemorySize=8bytes | | Sort Key: #[0,1] ASC | | Limit: CASE WHEN (@probe_limit IS NULL) THEN 1 ELSE cast(@probe_limit AS BIGINT) END | | -> Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=32bytes OutputSize=16bytes MemorySize=8bytes | | -> Join | | Analyze: timeConsumed=0ms probe_time=[0ms,0ms] build_time=[0ms] waitTime=0ms inputRows=2 outputRows=1 InputSize=48bytes OutputSize=32bytes MemorySize=49331bytes | | Join Type: SINGLE | | Join Cond: (__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861.__mo_index_centroid_version = #[1,0]) | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7c6d-8723-e83fd59a2861 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=40bytes OutputSize=40bytes MemorySize=80bytes | | -> Project | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=76bytes OutputSize=8bytes MemorySize=0bytes | | -> Filter | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=1 InputSize=196bytes OutputSize=76bytes MemorySize=79bytes | | Filter Cond: (__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981.__mo_index_key = 'version') | | -> Table Scan on a.__mo_index_secondary_018ea257-8e91-7f66-a497-47c559364981 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=196bytes OutputSize=196bytes MemorySize=392bytes | | -> Table Scan on a.vector_index_04 | | Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=9 InputSize=468bytes OutputSize=468bytes MemorySize=936bytes | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 50 rows in set (0.00 sec) mysql> ```
arjunsk commented 7 months ago

1.a Only l2_distance

Takes: 3mins 5 secs

Read More ```sql select min( l2_distance(`centroids`.`__mo_index_centroid`, `tbl`.`__mo_org_tbl_norm_vec_col`)), __mo_org_tbl_pk_may_serial_col FROM (SELECT `a` AS `__mo_org_tbl_pk_may_serial_col`, `b` AS `__mo_org_tbl_norm_vec_col` FROM `t3`) AS `tbl` CROSS JOIN (SELECT * FROM `__mo_index_secondary_018ec6a6-3879-7019-9859-3bf473bfcab0`) as centroids GROUP BY `centroids`.`__mo_index_centroid_version`, __mo_org_tbl_pk_may_serial_col; ```

1.b l2_distance + normalize_l2

Time: 3mins 13secs

Read More ```sql select min( l2_distance(`centroids`.`__mo_index_centroid`, `tbl`.`__mo_org_tbl_norm_vec_col`)), __mo_org_tbl_pk_may_serial_col FROM (SELECT `a` AS `__mo_org_tbl_pk_may_serial_col`, normalize_l2(`b`) AS `__mo_org_tbl_norm_vec_col` FROM `t3`) AS `tbl` CROSS JOIN (SELECT * FROM `__mo_index_secondary_018ec6a6-3879-7019-9859-3bf473bfcab0`) as centroids GROUP BY `centroids`.`__mo_index_centroid_version`, __mo_org_tbl_pk_may_serial_col; ```

2.a Only serial_full and serial_extract

Time: 1min 45 secs

Read More ```sql explain analyze select centroid_version, serial_extract(min(serial_full( atan(norm_l2_vector, centroid), centroid_id )), 1 as bigint), pk from (select 0 as centroid_version, result as centroid_id, result as centroid from generate_series(1, 500) g) as t1 cross join (select result as pk, sqrt(result) as norm_l2_vector from generate_series(1, 1000000) g) as t2 group by centroid_version, pk; ```

3.a l2_distance + normalize_l2 + serial_full

Time : 4mins 2secs

Read More ```sql select min( serial_full( l2_distance(`centroids`.`__mo_index_centroid`, `tbl`.`__mo_org_tbl_norm_vec_col`), `centroids`.`__mo_index_centroid_id`)), __mo_org_tbl_pk_may_serial_col FROM ( SELECT `a` AS `__mo_org_tbl_pk_may_serial_col`, (`b`) AS `__mo_org_tbl_norm_vec_col` FROM `t3`) AS `tbl` CROSS JOIN ( SELECT * FROM `__mo_index_secondary_018ec6a6-3879-7019-9859-3bf473bfcab0`) as centroids GROUP BY `centroids`.`__mo_index_centroid_version`, __mo_org_tbl_pk_may_serial_col; ```

3.b l2_distance + normalize_l2 + serial_full + serial_extract

Time: 4mins 4 secs

Read More ```sql select serial_extract( min( serial_full( l2_distance(`centroids`.`__mo_index_centroid`, `tbl`.`__mo_org_tbl_norm_vec_col`), `centroids`.`__mo_index_centroid_id`)), 1 AS bigint), __mo_org_tbl_pk_may_serial_col FROM ( SELECT `a` AS `__mo_org_tbl_pk_may_serial_col`, (`b`) AS `__mo_org_tbl_norm_vec_col` FROM `t3`) AS `tbl` CROSS JOIN ( SELECT * FROM `__mo_index_secondary_018ec6a6-3879-7019-9859-3bf473bfcab0`) as centroids GROUP BY `centroids`.`__mo_index_centroid_version`, __mo_org_tbl_pk_may_serial_col; ```