2.2 Optimizing updates and Deletes , EVALUATE_DELETE_PERFORMANCE

Best Practices for Update and Delete:

1. HP Vertica is optimized for query-intensive workloads, so DELETE and UPDATE queries might not achieve the same level of performance as other queries.

2. DELETE and UPDATE operations go to the WOS by default, but if the data is sufficiently large and would not fit in memory, HP Vertica automatically switches to using the ROS

3. HP Vertica does not remove deleted data immediately but keeps it as history for the purposes of historical query . A large amount of history can result in slower query performance. 

4. A large number of (unpurged) deleted rows can negatively affect query performance.
   To eliminate rows that have been deleted from the result, a query must do extra processing.
   If 10% or more of the total rows in a table have been deleted, the performance of a query on the table degrades. 

5. If a table has a large number of deleted rows, consider purging those rows to improve performance. 

6. Recovery is the action required for a cluster to restore K-safety after a crash. Large numbers of deleted records can degrade the performance of a recovery. To improve recovery performance, purge the deleted rows. 

Optimizing DELETEs and UPDATEs for Performance
The following sections describe several ways to improve projection design and improve DELETE and UPDATE queries to significantly increase DELETE and UPDATE performance.

1. When all columns required by the DELETE or UPDATE predicate are present in a projection, the projection is optimized for DELETEs and UPDATEs. DELETE and UPDATE operations on such projections are significantly faster than on non-optimized projections.
   Both simple and pre-join projections can be optimized.
   For example, consider the following table and projections:
   CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER);CREATE PROJECTION p1 (a, b, c) AS SELECT * FROM t ORDER BY a;CREATE PROJECTION p2 (a, c) AS SELECT a, c FROM t ORDER BY c, a;
   
   In the following query, both p1 and p2 are eligible for DELETE and UPDATE optimization because column a is available:
   DELETE from t WHERE a = 1;
   
   In the following example, only projection p1 is eligible for DELETE and UPDATE optimization because the b column is not available in p2:
   DELETE from t WHERE b = 1;

Optimized DELETEs are not supported under the following conditions:
 

• With pre-join projections on nodes that are down
   

• With replicated and pre-join projections if subqueries reference the target table. For example, the following syntax is not supported:
  DELETE FROM tb1 WHERE tb1.a IN (SELECT e FROM tb2, tb2 WHERE tb2.e = tb1.e);
   
• With subqueries that do not return multiple rows. For example, the following syntax is not supported:
  DELETE FROM tb1 WHERE tb1.a = (SELECT k from tb2);

2. Design your projections so that frequently-used DELETE or UPDATE predicate columns appear in the sort order of all projections for large DELETEs and UPDATEs.
   For example, suppose most of the DELETE queries you perform on a projection look like the following:DELETE from t where time_key < '1-1-2007'
   To optimize the DELETEs, make time_key appear in the ORDER BY clause of all your projections. This schema design results in better performance of the DELETE operation.

3. To analyze projections for sort order issues, use the EVALUATE_DELETE_PERFORMANCE function.(evaluates delete and update performance issues)
   This function Evaluates projections for potential DELETE performance issues. If there are issues found, a warning message is displayed. 
   
   This function uses data sampling to determine whether there are any issues with a projection. Therefore, it does not generate false-positives warnings, but it can miss some cases where there are performance issues.
   Optimizing for delete performance is the same as optimizing for update performance. So, you can use this function to help optimize a projection for updates as well as deletes.
   Syntax:
   EVALUATE_DELETE_PERFORMANCE ( 'target' )

target

The name of a projection or table. If you supply the name of a projection, only that projection is evaluated for DELETE performance issues. If you supply the name of a table, then all of the projections anchored to the table will be evaluated for issues. If you do not provide a projection or table name, EVALUATE_DELETE_PERFORMANCE examines all of the projections that you can access for DELETE performance issues. Depending on the size you your database, this may take a long time.

When evaluating multiple projections, EVALUATE_DELETE_PERFORMANCE reports up to ten projections that have issues, and refers you to a table that contains the full list of issues it has found.

The following example demonstrates how you can use EVALUATE_DELETE_PERFORMANCE to evaluate your projections for slow DELETE performance.

=> create table example (A int, B int,C int);
CREATE TABLE
=> create projection one_sort (A,B,C) as (select A,B,C from example) order by A;
CREATE PROJECTION
=> create projection two_sort (A,B,C) as (select A,B,C from example) order by A,B;
CREATE PROJECTION
=> select evaluate_delete_performance('one_sort');
            evaluate_delete_performance           
---------------------------------------------------
 No projection delete performance concerns found.
(1 row)
=> select evaluate_delete_performance('two_sort');
            evaluate_delete_performance           
---------------------------------------------------
 No projection delete performance concerns found.
(1 row)