Optimize SHOW TABLES

[findepi]

When using SHOW TABLES against Hive connector, the Metastore is queries twice.
This is because

• io.prestosql.sql.rewrite.ShowQueriesRewrite.Visitor#visitShowTables delegates SHOW TABLES to information_schema.tables
• information_schema.tables differentiates between tables and views, so we query Metastore twice in https://github.com/prestosql/presto/blob/8f949a79585387614d428fe2a3d0ab84b781ab12/presto-main/src/main/java/io/prestosql/connector/informationschema/InformationSchemaPageSourceProvider.java#L182-L183

Since SHOW TABLES does not differentiate between tables and views, we could do this faster.

[kokosing]

Does it actually do a call to HMS? Are you sure that results are not taken from cache?

[Praveen2112]

But if the cache is disabled it would fetch from the HMS right ?

[kokosing]

Transactional cache is always enabled. We do not query HMS twice for the same thing within the same transaction.

[findepi]

@kokosing there are 2 different calls to HMS.
The one that we don't need is this one:
https://github.com/prestosql/presto/blob/8003a11caaea40ce349ef33746c8537c35619122/presto-hive/src/main/java/io/prestosql/plugin/hive/metastore/thrift/ThriftHiveMetastore.java#L726-L727

[findepi]

Probably best fix would be to make information schema aware of selected columns.
When table_type from information_schema.tables is not needed, InformationSchemaPageSourceProvider#buildTables wouldn't need to list views.

[slinjhu]

It takes forever when I do "SHOW TABLES" for ~6k tables. Any update on the issue?