Introduce INDEX ON syntax and use it to implement indexes of vector types #3786
Description
Introduce a new, more concise syntax, regarding the organization of index keys and values, for defining indexes in the relational layer: the INDEX ON syntax. This provides an alternative to the existing INDEX AS SELECT (materialized view) approach while producing semantically equivalent indexes and query plans. It also introduces new type of index, the vector index, which can be defined exclusively with the new index syntax.
Comparison with current INDEX AS SELECT DDL syntax:
Both syntaxes are fully supported and produce equivalent indexes:
| Feature | INDEX AS SELECT | INDEX ON |
|---|---|---|
| Regular indexes | CREATE INDEX idx AS SELECT col FROM t ORDER BY col | CREATE INDEX idx ON t(col) |
| Multi-column | CREATE INDEX idx AS SELECT a, b FROM t ORDER BY a, b | CREATE INDEX idx ON t(a, b) |
| Covering index | CREATE INDEX idx AS SELECT a, b, c FROM t ORDER BY a | CREATE INDEX idx ON t(a) INCLUDE(b, c) |
| Descending | CREATE INDEX idx AS SELECT col FROM t ORDER BY col DESC | CREATE INDEX idx ON t(col DESC) |
| Null ordering | CREATE INDEX idx AS SELECT col FROM t ORDER BY col NULLS LAST | CREATE INDEX idx ON t(col NULLS LAST) |
| Aggregates | CREATE INDEX idx AS SELECT SUM(x) FROM t GROUP BY y | CREATE VIEW v AS SELECT SUM(x) AS s, y FROM t GROUP BY y;CREATE INDEX idx ON v(y) INCLUDE(s) |