Querying v_catalog.comments is very slow even with no results

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.

Tagged:

Comments

  • Jim_KnicelyJim_Knicely Administrator
    edited November 2021

    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).

  • verticauser810verticauser810 Vertica Customer

    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 ms
    

    Here 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 ms
    
  • verticauser810verticauser810 Vertica Customer
    edited November 2021

    Thank 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".

    Access Path:
    +-JOIN HASH [LeftOuter] [Cost: 80K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
    |  Join Cond: (constraints.oid = c.objectoid)
    |  Materialize at Input: c.objectoid, constraints.oid, constraints.name, constraints."schema", constraints.relid
    |  Materialize at Output: c.oid, c.objecttype, c.owner, c.creationtime, c.lastmodifiedtime, c.comment
    |  Execute on: Query Initiator
    | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 66K, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
    | |      Join Cond: (projection_columns.column_id = c.objectoid)
    | |      Materialize at Input: c.objectoid
    | |      Execute on: Query Initiator
    | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 52K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
    | | |      Join Cond: (sequences.oid = c.objectoid)
    | | |      Materialize at Input: c.objectoid, sequences.oid, sequences.name, sequences."schema"
    | | |      Execute on: Query Initiator
    | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 43K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
    | | | |      Join Cond: (tables.table_id = c.objectoid)
    | | | |      Materialize at Input: c.objectoid
    | | | |      Execute on: Query Initiator
    | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 37K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
    | | | | |      Join Cond: (libs.lib_oid = c.objectoid)
    | | | | |      Materialize at Input: c.objectoid
    | | | | |      Execute on: Query Initiator
    | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 14K, Rows: 10K (NO STATISTICS)] (PATH ID: 6)
    | | | | | |      Join Cond: (procs.proc_oid = c.objectoid)
    | | | | | |      Materialize at Input: c.objectoid
    | | | | | |      Execute on: Query Initiator
    | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 10K, Rows: 10K (NO STATISTICS)] (PATH ID: 7)
    | | | | | | |      Join Cond: (nodes.oid = c.objectoid)
    | | | | | | |      Materialize at Input: c.objectoid
    | | | | | | |      Execute on: Query Initiator
    | | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 8K, Rows: 10K (NO STATISTICS)] (PATH ID: 8)
    | | | | | | | |      Join Cond: (views.table_id = c.objectoid)
    | | | | | | | |      Materialize at Input: c.objectoid
    | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 5K, Rows: 10K (NO STATISTICS)] (PATH ID: 9)
    | | | | | | | | |      Join Cond: (projections.oid = c.objectoid)
    | | | | | | | | |      Materialize at Input: c.objectoid
    | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 10)
    | | | | | | | | | |      Join Cond: (schemata.oid = c.objectoid)
    | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | | +-- Outer -> STORAGE ACCESS for c [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 11)
    | | | | | | | | | | |      Projection: v_internal.vs_comments_p
    | | | | | | | | | | |      Materialize: c.objectoid
    | | | | | | | | | | |      Filter: (c.commenttype = 1)
    | | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | | +-- Inner -> STORAGE ACCESS for schemata [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 12)
    | | | | | | | | | | |      Projection: v_internal.vs_schemata_p
    | | | | | | | | | | |      Materialize: schemata.oid, schemata.name
    | | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | +-- Inner -> STORAGE ACCESS for projections [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 13)
    | | | | | | | | | |      Projection: v_internal.vs_projections_p
    | | | | | | | | | |      Materialize: projections.oid, projections.name, projections.schemaname
    | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | +-- Inner -> STORAGE ACCESS for views [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 14)
    | | | | | | | | |      Projection: v_internal.vs_views_p
    | | | | | | | | |      Materialize: views.table_schema, views.table_id, views.table_name
    | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | +-- Inner -> STORAGE ACCESS for nodes [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 15)
    | | | | | | | |      Projection: v_internal.vs_nodes_p
    | | | | | | | |      Materialize: nodes.oid, nodes.name
    | | | | | | | |      Execute on: Query Initiator
    | | | | | | +-- Inner -> STORAGE ACCESS for procs [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 16)
    | | | | | | |      Projection: v_internal.vs_procedures_p
    | | | | | | |      Materialize: procs.proc_oid, procs.procedure_name, procs.schema_name, procs.procedure_type
    | | | | | | |      Execute on: Query Initiator
    | | | | | +-- Inner -> STORAGE ACCESS for libs [Cost: 10K, Rows: 10K (NO STATISTICS)] (PATH ID: 17)
    | | | | | |      Projection: v_monitor.user_libraries_p
    | | | | | |      Materialize: libs.schema_name, libs.lib_name, libs.lib_oid
    | | | | | |      Execute on: Query Initiator
    | | | | +-- Inner -> SELECT [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 18)
    | | | | |      Execute on: Query Initiator
    | | | | | +---> STORAGE ACCESS for t [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 19)
    | | | | | |      Projection: v_internal.vs_tables_view_p
    | | | | | |      Materialize: t.table_schema, t.table_id, t.table_name
    | | | | | |      Execute on: Query Initiator
    | | | +-- Inner -> JOIN HASH [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 20)
    | | | |      Join Cond: (sequences_schemata.oid = sequences."schema")
    | | | |      Execute on: Query Initiator
    | | | | +-- Outer -> STORAGE ACCESS for sequences [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 21)
    | | | | |      Projection: v_internal.vs_sequences_p
    | | | | |      Materialize: sequences."schema"
    | | | | |      Execute on: Query Initiator
    | | | | |      Runtime Filter: (SIP3(HashJoin): sequences."schema")
    | | | | +-- Inner -> STORAGE ACCESS for sequences_schemata [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 22)
    | | | | |      Projection: v_internal.vs_schemata_p
    | | | | |      Materialize: sequences_schemata.oid, sequences_schemata.name
    | | | | |      Execute on: Query Initiator
    | | +-- Inner -> SELECT [Cost: 8K, Rows: 10K (NO STATISTICS)] (PATH ID: 23)
    | | |      Execute on: Query Initiator
    | | | +---> JOIN HASH [LeftOuter] [Cost: 8K, Rows: 10K (NO STATISTICS)] (PATH ID: 24)
    | | | |      Join Cond: (tc.table_id = co.table_id) AND (tc.ordinal_position = co.table_position)
    | | | |      Execute on: Query Initiator
    | | | | +-- Outer -> JOIN HASH [Cost: 6K, Rows: 10K (NO STATISTICS)] (PATH ID: 25)
    | | | | |      Join Cond: (pc.proj = pr.oid)
    | | | | |      Materialize at Input: co.column_id, co.table_id, co.table_position
    | | | | |      Execute on: Query Initiator
    | | | | | +-- Outer -> SELECT [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 26)
    | | | | | |      Execute on: Query Initiator
    | | | | | | +---> GROUPBY HASH [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 27)
    | | | | | | |      Group By: vs_projections.oid, vs_projections.name
    | | | | | | |      Execute on: Query Initiator
    | | | | | | | +---> STORAGE ACCESS for vs_projections [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 28)
    | | | | | | | |      Projection: v_internal.vs_projections_p
    | | | | | | | |      Materialize: vs_projections.oid, vs_projections.name
    | | | | | | | |      Execute on: Query Initiator
    | | | | | | | |      Runtime Filters: (SIP1(HashJoin): pr.oid), (SIP1(HashJoin): pr.oid)
    | | | | | +-- Inner -> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 5K, Rows: 10K (NO STATISTICS)] (PATH ID: 29)
    | | | | | |      Join Cond: (pc.oid = pcap.column_id)
    | | | | | |      Execute on: Query Initiator
    | | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 4K, Rows: 10K (NO STATISTICS)] (PATH ID: 30)
    | | | | | | |      Join Cond: (pc.oid = pcop.column_id)
    | | | | | | |      Execute on: Query Initiator
    | | | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 31)
    | | | | | | | |      Join Cond: (pc.oid = pce.column_id)
    | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 32)
    | | | | | | | | |      Join Cond: (co.column_id = pc.oid)
    | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | +-- Outer -> STORAGE ACCESS for co [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 33)
    | | | | | | | | | |      Projection: v_internal.vs_projection_column_order_p
    | | | | | | | | | |      Materialize: co.column_id
    | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | | |      Runtime Filter: (SIP2(MergeJoin): co.column_id)
    | | | | | | | | | +-- Inner -> STORAGE ACCESS for pc [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 34)
    | | | | | | | | | |      Projection: v_internal.vs_projection_columns_p
    | | | | | | | | | |      Materialize: pc.name, pc.oid, pc.proj
    | | | | | | | | | |      Filter: (pc.name <> 'epoch')
    | | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | | +-- Inner -> STORAGE ACCESS for pce [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 35)
    | | | | | | | | |      Projection: v_internal.vs_projection_column_expressions_p
    | | | | | | | | |      Materialize: pce.column_id
    | | | | | | | | |      Execute on: Query Initiator
    | | | | | | | +-- Inner -> STORAGE ACCESS for pcop [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 36)
    | | | | | | | |      Projection: v_internal.vs_projection_column_aggregate_order_p
    | | | | | | | |      Materialize: pcop.column_id
    | | | | | | | |      Execute on: Query Initiator
    | | | | | | +-- Inner -> STORAGE ACCESS for pcap [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 37)
    | | | | | | |      Projection: v_internal.vs_projection_column_aggregate_partition_p
    | | | | | | |      Materialize: pcap.column_id
    | | | | | | |      Execute on: Query Initiator
    | | | | +-- Inner -> SELECT [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 38)
    | | | | |      Execute on: Query Initiator
    | | | | | +---> STORAGE ACCESS for vcs [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 39)
    | | | | | |      Projection: v_internal.vs_columns_p
    | | | | | |      Materialize: vcs.nspname, vcs.t_oid, vcs.attnum
    | | | | | |      Filter: ((vcs.nspname <> 'v_internal') AND (vcs.nspname <> 'v_monitor') AND (vcs.nspname <> 'v_catalog'))
    | | | | | |      Execute on: Query Initiator
    | +-- Inner -> JOIN HASH [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 40)
    | |      Join Cond: (constraints_tables.table_id = constraints.relid)
    | |      Materialize at Input: constraints.relid
    | |      Execute on: Query Initiator
    | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 41)
    | | |      Join Cond: (constraints_schemata.oid = constraints."schema")
    | | |      Execute on: Query Initiator
    | | |      Runtime Filter: (SIP4(HashJoin): constraints.relid)
    | | | +-- Outer -> STORAGE ACCESS for constraints [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 42)
    | | | |      Projection: v_internal.vs_constraints_p
    | | | |      Materialize: constraints."schema"
    | | | |      Execute on: Query Initiator
    | | | +-- Inner -> STORAGE ACCESS for constraints_schemata [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 43)
    | | | |      Projection: v_internal.vs_schemata_p
    | | | |      Materialize: constraints_schemata.oid, constraints_schemata.name
    | | | |      Execute on: Query Initiator
    | | +-- Inner -> SELECT [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 44)
    | | |      Execute on: Query Initiator
    | | | +---> STORAGE ACCESS for t [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 45)
    | | | |      Projection: v_internal.vs_tables_view_p
    | | | |      Materialize: t.table_id, t.table_name
    | | | |      Execute on: Query Initiator
    
  • SruthiASruthiA Administrator

    @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=>

  • verticauser810verticauser810 Vertica Customer
    edited November 2021

    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.

  • SruthiASruthiA Administrator

    @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.

  • verticauser810verticauser810 Vertica Customer
    edited March 2022

    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:

    Vertica tables, views, columns comments will be cached lazy. Start the DBeaver version 22.0.1

    See the full comment on github.

  • SruthiASruthiA Administrator

    @verticauser810 : Thanks for sharing more details. DBViz version I am using is 12.1.8. Hence I might be not be experiencing that issue.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file