QUERY_EVENTS

Returns information about query planning, optimization, and execution events.

Informational event types

CSE ANALYSIS

The optimizer performed Common subexpressions analysis

CSE ANALYSIS STATS

Time spent on Common subexpressions analysis (msec)

EXPRESSION_EVAL_ERROR

An exception occurred during evaluation of an expression

EXTERNAL_DATA_FILES_PRUNED

Vertica skipped external data files (for example, Iceberg files) that could not satisfy predicates in the query. The event details include the number and paths of pruned files.

EXTERNAL_PREDICATE_PUSHDOWN_NOT_SUPPORTED

Predicate pushdown for older Hive versions may not be supported. For more information, see Querying external tables.

FLATTENED SUBQUERIES

Subqueries flattened in FROM clause

GROUP_BY_PREPASS_FALLBACK

Vertica could not run an optimization. In-memory prepass is disabled. The projection may not be optimal.

GROUPBY PUSHDOWN

Internal to Vertica

LibHDFS++ FAILOVER RETRY

Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.

LibHDFS++ MANUAL FALLBACK

Vertica accessed HDFS using the hdfs URL scheme but HDFSUseWebHDFS is set. Vertica fell back to WebHDFS.

LibHDFS++ UNSUPPORTED OPERATION

Vertica accessed HDFS using the hdfs URL scheme, but the HDFS cluster uses an unsupported feature such as wire encryption or HTTPS_ONLY or the Vertica session uses delegation tokens. Vertica fell back to WebHDFS.

MERGE_CONVERTED_TO_UNION

Vertica has converted a merge operator to a union operator due to the sort order of the multi-threaded storage access stream.

NO GROUPBY PUSHDOWN

Internal to Vertica

NODE PRUNING

Vertica performed node pruning, which is similar to partition pruning, but at the node level.

ORC_FILE_INFO

A query of ORC files encountered missing information (such as time zone) or an unrecognized ORC version. For missing information, Vertica uses a default value (such as the local time zone).

ORC_SOURCE_PRUNED

An entire ORC file was pruned during predicate pushdown.

ORC_STRIPES_PRUNED

The identified stripes were pruned during predicate pushdown. If an entire ORC file was pruned, it is instead recorded with an ORC_SOURCE_PRUNED event.

OUTER OVERRIDE NOT USED

Vertica found swapping inner/outer tables in a join unnecessary because the inner/outer tables were in good order. (For example, a smaller table was used in an inner join.)

OUTER OVERRIDE USED

For efficiency and optimization, Vertica has swapped the inner/outer tables in a join. Vertica used the smaller table as the inner table.

PARQUET_ROWGROUPS_PRUNED

The identified row groups were pruned during predicate pushdown.

PARTITION_PATH_PRUNED

A path (reported in event details) was pruned.

PARTITION_PATH_REJECTED

Could not evaluate partition column predicate on a path from source list. Path will be rejected.

PARTITION_PRUNING

COPY pruned partitions. The event reports how many paths were pruned, and PARTITION_PATH_PRUNED events record more details.

PREDICATES_DISCARDED_FROM_SCAN

Some predicates have been discarded from this scan because expression analysis shows they are not needed.

REJECT_ROWNUMS_HIT_BUFFER_LIMIT

Buffering row numbers during rejection hit buffer limit

SEQUENCE CACHE REFILLED

Vertica has refilled sequence cache.

SIP_FALLBACK

This optimization did not apply to this query type.

SMALL_MERGE_REPLACED

Vertica has chosen a more efficient way to access the data by replacing a merge.

STORAGE_CONTAINERS_ELIMINATED

Vertica has performed partition pruning for the purpose of optimization.

TRANSITIVE PREDICATE

Vertica has optimized by adding predicates to joins where it makes logical sense to do so.

For example, for the statement, SELECT * FROM A, B WHERE A.a = B.a AND A.a = 1;Vertica may add a predicate B a = 1 as a filter for better storage access of table B.

TYPE_MISMATCH_COLUMNS_PARQUETPARSER

The Parquet parser used loose schema matching to load data, and could not coerce values in the Parquet data to the types defined for the table. By default the parser rejects the row. For more information, see PARQUET.

UNMATCHED_TABLE_COLUMNS_PARQUETPARSER

The Parquet parser used loose schema matching to load data, and columns in the table had no corresponding columns in the data. The columns were given values of NULL.

VALUE_TRUNCATED

A character value is too long.

WEBHDFS FAILOVER RETRY

Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.

Warning event types

AUTO_PROJECTION_USED

The optimizer used an auto-projection to process this query.

Recommendation: Create a projection that is appropriate for this query and others like it; consider using Database Designer to generate query-specific projections.

GROUP_BY_SPILLED

This event type is typically related to a specific type of query, which you might need to adjust.

Recommendation: Identify the type of query and make adjustments accordingly. You might need to adjust resource pools, projections, or the amount of RAM available. Try running the query on a cluster with no additional workload.

INVALID COST

When creating a query plan, the optimizer calculated an invalid cost for a path: not-a-number (NaN) value, infinity value, or negative value. The path cost was set to its default value.

No action available to users.

PATTERN_MATCH_NMEE

More than one pattern event is true for a single row

Recommendation: Modify event expressions to ensure that only one event can be true for any row. Alternatively, modify the query using a MATCH clause with ROWS MATCH FIRST EVENT.

PREDICATE OUTSIDE HISTOGRAM

A predicate value you are trying to match does not exist in a set of possible values for a specific column. For example, you try to match a VARCHAR value WHERE mystring = "ABC<newline>". In this case, the newline character throws off the predicate matching optimizations.

Recommendation: Run ANALYZE_STATISTICS on the column.

RESEGMENTED_MANY_ROWS

This event type is typically related to a specific type of query, which you might need to adjust.

Recommendation: Do projections need to be segmented in a different way to allow for join locality? Can you rewrite the query to filter out more rows at storage access time? (Typically, Vertica does so automatically through predicate pushdown.) Review your explain plan.

RLE_OVERRIDDEN

The average run counts are not large enough for Run Length Encoding (RLE). This event occurs with queries where the filtered results for certain columns do not work with RLE because cardinality is less than 10.

Recommendation: Review and rewrite your query, if necessary.

Critical event types

DELETE WITH NON OPTIMIZED PROJECTION

One or more projections do not have your delete filter column in their sort order, causing Vertica difficulty identifying ros to mark as deleted.

To resolve: Add the delete filter column to the end of every projection sort order for your target delete table.

JOIN_SPILLED

Vertica has spilled a join to disk. A join spill event slows down the subject query and all other queries as it consumes resources while using disk as virtual memory.

To resolve: Try the following:

1. Review the explain plan. The query might be too ambitious, for example, cross joining two large tables.

2. Consider adding the query to a lower priority pool to reduce impact on other queries.

3. Create projections that allow for a merge join instead of a hash join.

4. Adjust the PLANNEDCONCURRENCYresource pool so that queries have more memory to execute.

MEMORY LIMIT HIT

Indicates query complexity or, possibly, lack of available system memory.

To resolve: Consider adjusting the MAXMEMORYSIZE and PLANNEDCONCURRENCY resource pools so that the optimizer has sufficient memory. On a heavily used system, this event may occur more frequently.

NO HISTOGRAM

Indicates a table does not have an updated column histogram.

To resolve: Running the function ANALYZE_STATISTICS most often corrects this issue.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

• EXECUTION_ENGINE_PROFILES
• QUERY_CONSUMPTION
• QUERY_PLAN_PROFILES