Does TiDB have virtual index functionality?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB是否具有虚拟索引功能?

| username: TiDBer_5MVonMJN

Does TiDB integrate functionality similar to PG’s virtual index HypoPG?

| username: Kongdom | Original post link

There should be no virtual index. :thinking:

| username: ShawnYan | Original post link

Currently, there isn’t one. What is the application scenario for this requirement?

| username: 我是咖啡哥 | Original post link

I don’t know what PG’s virtual index looks like, and I’m not sure if what you’re referring to is the following.
TiDB generated columns (virtual columns) can have indexes built on them.

Generated columns include stored generated columns and virtual generated columns. Stored generated columns store the computed values, so they don’t need to be recalculated when read. Virtual generated columns do not store their values and are recalculated when read. Compared to virtual generated columns, stored generated columns offer better performance when read but consume more disk space.

Indexes can be created on both stored generated columns and virtual columns.

| username: MrSylar | Original post link

– HypoPG virtual indexes are a PostgreSQL extension that provides a way to create and use virtual indexes in the database. Virtual indexes do not actually store data but improve query performance by simulating the behavior of indexes during the query process.

TiDB does not have a similar feature. If you want to evaluate whether an index is effective, can Invisible Indexes be used to achieve a similar result?

| username: redgame | Original post link

No, it’s not that practical.

| username: h5n1 | Original post link

TiDB does not have a similar feature. If you want to evaluate whether an index is effective, can you use Invisible Indexes to achieve a similar result?

Invisible indexes still require creating the index first, and then making it invisible. Once it is invisible, the optimizer will not use it.

| username: ShawnYan | Original post link

Are you planning to migrate from PostgreSQL to TiDB?

| username: MrSylar | Original post link

Yes, but after all, we don’t have it. We are in the MySQL family.

| username: TiDBer_5MVonMJN | Original post link

Is it true that apart from PostgreSQL, the mainstream DBMSs do not support the virtual index feature?

| username: zhanggame1 | Original post link

Oracle has virtual indexes that other databases don’t have, which seems a bit redundant.

| username: TiDBer_5MVonMJN | Original post link

I think this extension feature can avoid the overhead of actually creating indexes, which is quite useful?

| username: zhanggame1 | Original post link

It won’t work even if you build it, so it’s no different from not having it.

| username: TiDBer_5MVonMJN | Original post link

If the candidate set is very large, you can use this to initially filter out the useless ones, right?

| username: dba-kit | Original post link

First, let me answer the question: TiDB indeed does not support this plugin, and MySQL also does not have this feature. However, although it cannot simulate adding an index, it does support simulating invisible indexes.

Additionally, I looked into the functionality of the HypoPG plugin, which provides a simulated index feature to verify whether an index is effective for certain types of queries. Well, how should I put it, this feature is quite redundant. Although it allows you to verify the effectiveness of an index without actually creating it, generally speaking, there are not many points to pay attention to when it comes to indexes. As long as you find out which column in the WHERE condition has better selectivity, it will have the same effect as creating virtual indexes for each column. (Actually, you can tell by looking at the number of stars this plugin has; it’s not even a thousand…)

| username: TiDBer_5MVonMJN | Original post link

The selectivity of columns, especially complex predicates, should be difficult to calculate, right? Or does TiDB have related functionality to support this?

| username: dba-kit | Original post link

@TiDBer_5MVonMJN Oops, a correction: version 7.2 actually already supports virtual indexes, but it hasn’t been added to the documentation yet.
PS: @h5n1 discovered this. I just remembered you asked about this, so I’m sharing his experimental results.

| username: linnana | Original post link

Learned.

| username: TiDBer_vfJBUcxl | Original post link

:+1::+1::+1:

| username: ShawnYan | Original post link

The second screenshot