Querying v_catalog.comments is very slow even with no results
verticauser810
Vertica Customer ✭
When I query v_catalog.comments it takes well over a minute:
SELECT * FROM v_catalog.comments LIMIT 1;
Here are the results in DBeaver:
There is only one result returned, and that's because I explicitly added a comment to one table to test this query. Other than that one comment, no results are returned. It shouldn't be this slow. Other queries on this cluster seem to run fine.
I can't find much information on this comments table in Vertica documentation or in the Vertica database catalog itself. There is an entry for the comments table in all_tables but nowhere else. I can't tell if this is some kind of special table that is somehow backed by some unknown process that is in great need of optimization.
I don't care much about comments, but the problem is that DBeaver tries to gather object metadata for various user actions, and these queries seem to always involve the comments table. Any time you have auto-completion enabled, or when you expand nodes in the Database Manager pane, it tries to issue queries to Vertica to get information about the objects. This is especially true for any action that displays column information, like when you expand a table node, or double-click on a table. DBeaver is our company's main way of interacting with Vertica manually. I've tried to disable some of the metadata queries issued by DBeaver, but there are still many cases where the slowness of the comments table gets in the way. If you do the wrong thing, you have to wait a long minute for the GUI to update. As soon as the query involving the comments table finishes, the GUI then updates and you can continue working.
Our Vertica cluster is version 9.2.0-4.
Comments
Can you try it from vsql?
dbadmin=> \timing on
Timing is on.
dbadmin=> SELECT * FROM v_catalog.comments LIMIT 1;
comment_id | object_id | object_type | object_schema | object_name | child_object | owner_id | owner_name | creation_time | last_modified_time | comment
------------+-----------+-------------+---------------+-------------+--------------+----------+------------+---------------+--------------------+---------
(0 rows)
Time: First fetch (0 rows): 601.816 ms. All rows formatted: 601.879 ms
dbadmin=> CREATE TABLE some_table(c1 INT);
CREATE TABLE
Time: First fetch (0 rows): 10.699 ms. All rows formatted: 10.718 ms
dbadmin=> COMMENT ON TABLE some_table IS 'Does this slow down querying the COMMENTS table?';
COMMENT
Time: First fetch (0 rows): 8.094 ms. All rows formatted: 8.112 ms
dbadmin=> SELECT * FROM v_catalog.comments LIMIT 1;
comment_id | object_id | object_type | object_schema | object_name | child_object | owner_id | owner_name | creation_time | last_modified_time | comment
-------------------+-------------------+-------------+---------------+-------------+--------------+-------------------+------------+-------------------------------+-------------------------------+--------------------------------------------------
49539595901858026 | 49539595901858024 | TABLE | public | some_table | | 45035996273704962 | dbadmin | 2021-11-16 03:43:57.951764+00 | 2021-11-16 03:43:57.951764+00 | Does this slow down querying the COMMENTS table?
(1 row)
Time: First fetch (1 row): 617.007 ms. All rows formatted: 617.110 ms
But V_CATALOG.COMMENTS is actually a pretty complex view (see attachment).
Hi Jim! Thanks for looking into this with me.
shared8=> SELECT VERSION(); VERSION ------------------------------------ Vertica Analytic Database v9.2.0-4 (1 row) shared8=> \timing on Timing is on. shared8=> SELECT * FROM v_catalog.comments LIMIT 1;This took a really long time this time:
comment_id | object_id | object_type | object_schema | object_name | owner_id | owner_name | creation_time | last_modified_time | comment -------------------+-------------------+-------------+-----------------+-------------+-------------------+---------------------+ -------------------------------+-------------------------------+----------------------------- 45035997763150894 | 54043196658702216 | TABLE | prodarch1_stage | vwDummy | 45035997151248722 | prodarch1_stage_etl | 2021-11-15 16:12:34.274058-08 | 2021-11-15 16:12:34.274058-08 | Test Comment on Dummy Table (1 row) Time: First fetch (1 row): 256835.000 ms. All rows formatted: 256900.000 msHere is the test involving creating a table and adding a comment to it:
shared8=> CREATE TABLE some_table(c1 INT); WARNING 6978: Table "some_table" will include privileges from schema "prodarch1_stage" CREATE TABLE Time: First fetch (0 rows): 1417.000 ms. All rows formatted: 1418.000 ms shared8=> COMMENT ON TABLE some_table IS 'Does this slow down querying the COMMENTS table?'; COMMENT Time: First fetch (0 rows): 1079.000 ms. All rows formatted: 1080.000 ms shared8=> SELECT * FROM v_catalog.comments LIMIT 1; comment_id | object_id | object_type | object_schema | object_name | owner_id | owner_name | creation_time | last_modified_time | comment -------------------+-------------------+-------------+-----------------+-------------+-------------------+---------------------+ -------------------------------+-------------------------------+----------------------------- 45035997763150894 | 54043196658702216 | TABLE | prodarch1_stage | vwDummy | 45035997151248722 | prodarch1_stage_etl | 2021-11-15 16:12:34.274058-08 | 2021-11-15 16:12:34.274058-08 | Test Comment on Dummy Table (1 row) Time: First fetch (1 row): 222601.000 ms. All rows formatted: 222668.000 msThank you for the hint on querying vs_system_views to find the DDL for the comments system view.
Below is the EXPLAIN PLAN for the comments view query in our database. It's slightly different from the one you attached, as ours doesn't reference vs_sub_comments. Also, this query is slightly pared down because I don't have access to v_internal.vs_users. The main thing that stands out is that most of the joins in the view have "NO STATISTICS".
@verticauser810 : I just tried to reproduce the issue in latest versions. it is completing faster. Even in my cluster, Explain plan shows "NO STATISTICS" and references vs_sub_comments. Please try running the test in 10.x or 11.x
dbadmin=> CREATE TABLE some_table(c1 INT);
CREATE TABLE
dbadmin=> COMMENT ON TABLE some_table IS 'Does this slow down querying the COMMENTS table?';
COMMENT
dbadmin=> \timing on
Timing is on.
dbadmin=> SELECT * FROM v_catalog.comments LIMIT 1;
comment_id | object_id | object_type | object_schema | object_name | child_object | owner_id | owner_name | creation_time | last_modified_time | comment
-------------------+-------------------+-------------+---------------+-------------+--------------+-------------------+------------+-------------------------------+-------------------------------+--------------------------------------------------
45035996273727568 | 45035996273727566 | TABLE | public | some_table | | 45035996273704962 | dbadmin | 2021-11-19 11:59:17.862765-05 | 2021-11-19 11:59:17.862765-05 | Does this slow down querying the COMMENTS table?
(1 row)
Time: First fetch (1 row): 443.085 ms. All rows formatted: 443.194 ms
dbadmin=> select version();
version
Vertica Analytic Database v10.1.1-6
(1 row)
Time: First fetch (1 row): 10.707 ms. All rows formatted: 10.774 ms
dbadmin=>
Hi @SruthiA , I will try to get access to a v10 cluster. For us, v10 is still in the experimental phase (using EON), so it may not be a realistic comparison at this point. On our operational v9 cluster where the test above was done, we have over 150,000 non-system tables across ~200 non-system schemas. There are over 2.5 million total columns in these tables. I'm curious how that compares to the v10 cluster you used above. If you haven't reproduced the problem on a v9 cluster then I doubt it will exhibit on your v10 cluster.
Also, do you know of any improvements to the comments system view in v10? I wonder if it still has the same complexity, with so many joins and unions built on top of so many other views.
I have requested an enhancement to the DBeaver IDE to give the option to avoid the comments system view when gathering object metadata. I don't want to give the DBeaver team false hope that this issue is fixed in v10, otherwise they may forego the request and their product will still be slow when connecting to higher versions of Vertica.
@verticauser810 : Thank you for sharing more information. my v10 cluster is almost empty. it just has couple of small tables. I will try to run it in v9 cluster and see if it makes any difference.
The DBeaver team has worked around this problem with the slow comments view by changing to lazy caching, and avoiding the JOIN to the comments view which slowed down their UI:
See the full comment on github.
@verticauser810 : Thanks for sharing more details. DBViz version I am using is 12.1.8. Hence I might be not be experiencing that issue.