Skip to main content

Querying v_catalog.comments is very slow even with no results

  • November 16, 2021
  • 7 replies
  • 6 views
verticauser810
Forum|alt.badge.img

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.

    7 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • November 16, 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).

    verticauser810
    Forum|alt.badge.img
    • verticauser810
    • Author
    • Participating Frequently
    • November 16, 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
    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • November 19, 2021

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

    verticauser810
    Forum|alt.badge.img
    • verticauser810
    • Author
    • Participating Frequently
    • November 22, 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.

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • November 22, 2021

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

    verticauser810
    Forum|alt.badge.img
    • verticauser810
    • Author
    • Participating Frequently
    • March 15, 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.

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • March 15, 2022

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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement