Query Performance
Explanation
By default, queries do not use 
snapshot isolation and thus hold locks on the projections associated with the tables in the SELECT list. This blocks other database operations (concurrent queries) until the transaction ends.
Vertica can run any SQL query in snapshot isolation mode in order to obtain the fastest possible execution. To be precise, snapshot isolation mode is actually a form of historical query. The syntax is:
Vertica can execute a query from a snapshot of the database taken at a specific date and time. The syntax is:
AT TIME 'timestamp' SELECT...
The command queries all data in the database up to and including the epoch representing the specified date and time without holding a lock or blocking write operations.
An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.
AT EPOCH LATEST SELECT...
The command queries all data in the database up to but not including the current epoch without holding a lock or blocking write operations. This may cause the query to miss tuples loaded by other users up to (but no more than) a specific number of minutes before execution.
An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.
Workaround
Unless you need the result set of your query to include data from the current epoch, use:
AT EPOCH LATEST SELECT ...
If you need the result set of your query to include data from the current epoch, end the current transaction as soon as possible. This can be done by executing COMMIT or ROLLBACK or terminating the connection.
Explanation
Because Vertica does not support cursors, a query that produces a large result set can consume a great deal of client resources.
Workaround
- Add predicates to the query to reduce the size of the result set.
- Use the LIMIT clause (described in the SQL Reference Manual) to reduce the size of the result set.
