schema and table_name fields are empty in QUERY_PROFILES table

victtim

I’m trying to profile some queries using syntax like

PROFILE SELECT /+label(profile_query)/ COUNT(*) FROM…;

Per Vertica documentation, I can then query the QUERY_PROFILES table (see below) to get statistics like how long the query took. However, the “table_name” in this query is always empty, and I don’t quite understand why. I tried google/SO, to no avail. Just wondering if you may have any insight on this issue.

Jim_Knicely

Hi,

Currently the schema_name and table_name displayed in the query_profiles view are populated only for LOAD requests.

Example:

dbadmin=> create table p (i int);
CREATE TABLE

dbadmin=> copy p from '/home/dbadmin/p.txt';
Rows Loaded
-------------
1
(1 row)

dbadmin=> select schema_name, table_name, query_type, query from query_profiles where query_type = 'LOAD' order by query_start desc limit 1;
schema_name | table_name | query_type |               query
------------+------------+------------+------------------------------------
public      | p          | LOAD       | copy p from '/home/dbadmin/p.txt';
(1 row)

Most queries reference more than a single schema/table. Listing all of them wouldn't be all that useful.

victtim

Thank you! It would be good if this can be mentioned in the docs.

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PROFILES.htm

AMiller

I will submit an enhancement request to improve our documentation. Thank you for the feedback.