[Feature Request] In PosgresSQL indexes on foriegn key don't show up as indexes.

[justinmclean]

Version

main branch

Describe what's wrong

If you create a foreign key and index it doesn't show up as an index in the REST result.

Error message and/or stacktrace

N/A

How to reproduce

Using the playground, create an index on a foreign key like so:

alter table hr.employees add constraint FK_departments foreign key(department_id) references hr.departments(department_id);
create index on hr.employees(department_id);

Run this:

curl http://localhost:8090/api/metalakes/metalake_demo/catalogs/catalog_postgres/schemas/hr/tables/employees 

And indexes will be an empty array.

Additional context

No response

[FANNG1]

seems not a bug, Gravitino currently supports PRIMARY_KEY and UNIQUE_KEY only

[justinmclean]

In general foreign keys are much more common than primary or unique keys and provide important information on what fields are optimised to use in a query. Is supporting foreign key indexes or other indexes being considered?

[FANNG1]

this's not scheduled in 0.5, maybe we could consider scheduling in 0.6

[justinmclean]

The issue for that added indexes are a little misleading or could do with more description e.g. #1736 says "Support PostgreSQL index".

[justinmclean]

The release notes for 0.4 are also a light on details on this https://github.com/datastrato/gravitino/releases/tag/v0.4.0

[yuqi1129]

We do not support foreign index for PG, I will schedule it to 0.7.0.