The use of indexes can enhance YugabyteDB’s performance by enabling the database server to find rows faster. You can create, drop, and list indexes, as well as use indexes on expressions.
We learned in a previous YugabyteDB tip that a table can have at most 1,600 columns.
Is there a limit on the number of columns that an index can support?
Let’s find out!
First, I will create at table that has 1,600 columns.
[root@localhost ~]# alias y_217
alias y_217='/root/yugabyte-2.17.0.0/bin/ysqlsh -h 198.xx.xx.xxx -U yugabyte'
[root@localhost ~]# y_217 -Atc "SELECT 'DROP TABLE IF EXISTS test_table; CREATE TABLE test_table (' UNION ALL SELECT 'col' || generate_series || CASE WHEN generate_series <1600 THEN ' INT,' ELSE ' INT);' END FROM generate_series(1, 1600);" | y_217
NOTICE: table "test_table" does not exist, skipping
DROP TABLE
CREATE TABLE
[root@localhost ~]# y_217 -c "SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'test_table';"
count
-------
1600
(1 row)
Next I will try to create an index that also has 1,600 column in it’s definition.
[root@localhost ~]# y_217 -Atc "SELECT 'DROP TABLE IF EXISTS test_table_idx; CREATE INDEX test_table_idx ON test_table(' UNION ALL SELECT 'col' || generate_series || CASE WHEN generate_series <1600 THEN ',' ELSE ');' END FROM generate_series(1, 1600);" | y_217
NOTICE: table "test_table_idx" does not exist, skipping
DROP TABLE
ERROR: cannot use more than 32 columns in an index
There’s our answer… An index can have only up to 32 columns.
Have Fun!
