Slow queries against v_catalog.view_columns

Gregory_William

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.

[Deleted User]

Hi Gregory, thanks for reporting your performance. We'll let support know and see if this is a problem or standard performance for this kind of query.

Gregory_William

Please could you provide an update on this.

[Deleted User]

Hi Gregory, you are correct; Vertica is generating the virtual view view_columns first. Currently there is no direct way around this. Vertica system tables are tuned for an update-heavy workload, rather than a query-heavy workload like regular Vertica tables. If your schema is relatively static, you will likely get better performance by running a statement such as "CREATE TABLE view_columns_snapshot AS SELECT * FROM view_columns;", then selecting from the new table "view_columns_snapshot". You can further improve performance by optimizing the layout of this table's projections for the sorts of queries that you'll be running, either manually or with the Database Designer. Vertica can currently filter much earlier against regular tables than it can against system tables. Of course, then the snapshot table won't be kept in sync with the database's actual schema. You'd have to update it as needed, which is why this only works if the schema is relatively static. If system table performance is important to you, please feel free to post an Idea about it here, and/or contact your sales rep to let them know that it's a concern for you.