Vertica Diagnostic Queries April 2014

The April 2014 release has the following changes:

  • Modified raw estimate data space utilization to only include super projections
  • Removed suggestion to disable global profiling when not in use
  • Added 6 queries
    • Fault tolerance of system by returning K-safety level and number of node failures before automatic shut down
    • Overview of event types
    • Queries that spilled to disk during execution
    • Query events in which rows were resegmented during execution
    • Possible data skew in segmented projections
    • Last run and interval for each service on each node

Continue Reading


Vertica Join Operations

There are two operator types, merge and hash; each which can be performed on the local node (a local join) or across two or more nodes (a network join). This post will discuss the two operator types and the differences between a local and network join. The mechanics of each operator may be discussed in future posts.
Continue Reading


Vertica Diagnostic Queries February 2014

This is the first release of a monthly (hopefully) diagnostic query file for Vertica. It’s my intention to provide as much information as possible to help with administration. Although this release only contains 12 queries, I have to start somewhere. The idea is heavily inspired by Glenn Barry’s SQL Server diagnostic queries and Ola Hallengren’s SQL Server maintenance solution.
Continue Reading


Vertica Time Series with Last Value

This post will briefly cover working with time series stock data in Vertica. In my first experience, I use the TIME_SLICE function to aggregate and window the data as described in documentation. However, due to a lack of a window frame, I received incorrect results when paired with LAST_VALUE. After adding a window frame, the expected results were returned. This post will show how to alternatively return the desired results using the TIMESERIES clause and lastly compare the plans and performance of both.
Continue Reading