Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Slow queries against v_catalog.view_columns
We have a need to query v_catalog.view_columns in order to determine the data type of each column in a view. We use a query like this: select column_name, data_type from view_columns where table_schema = 'some_schema' and table_name = 'some_table' order by ordinal_position; However the performance of this is quite slow; using timing puts it at 31s, whereas a similar query looking at a table's columns takes only 25ms. I realise that it's more complex to determine the data type for the columns in the view, however I wonder whether there are any plans to improve the performance here? To give an idea of scales in our situation our database has 213,268 view columns spread across 2,226 views contained across 38 schemata. I suspect that internally Vertica is generating the entire contents of the virtual view view_columns first and is then subsequently performing the filter on the schema & view name. If this is the case and the filter could be executed earlier that would clearly improve the performance dramatically in our situation.