VAST Database supports predicate and dereference pushdowns.
A predicate is an expression that will evaluate to TRUE, FALSE or UNKNOWN. For example, comparison operations in SQL (>, <, =, >=, <=). In effect, this means that VAST Database performs the filtering operations in the WHERE clause of an SQL statement. See Supported Predicates for a complete list.
Here are some examples of push-downs supported by the VAST Database:
Column projection push-downs, where only the requested columns in the query are returned to the execution engine - as opposed to entire rows.
Column expression projection, where a computation of a column can be returned (for example
columnA > 7).A while table count aggregation (“
SELECT COUNT(*)....”) can be pushed-down from the query engine, where the VAST Database will return only the count result. Note that this does not apply to aggregations of query results. For instance:“
SELECT COUNT(*) FROM my_table;” will return a count value without forcing the engine to read the contents of the entire column.“
SELECT COUNT(*) FROM my_table WHERE my_column > 100;” will process the predicate and return the values to the query engine where the count aggregation is done.No other aggregations are supported in this manner.
If a database query includes requests that are not supported by VAST Database, the query engine generates a query execution plan to perform those operations after receiving filtered data back from VAST.
In the following example, the WHERE clauses would be processed as pushdowns to VAST Database and then the returned data would be averaged by the query engine:
SELECT AVG(col1) FROM vast.schema.mytable WHERE col1 > 100 AND col3 < 50;
Supported Predicates
VAST Database supports these predicates:
Predicate | Notes |
|---|---|
=, >, <, >=, <= | Equality, greater-than, less-than, etc |
IS (NOT) NULL | |
predicate1 AND predicate2 AND ... predicateN | |
x contained in {[1,3], [7,9), (14,inf), {NULL}} | |
x = 5 OR x = 9 | OR operations on the same column |
col1 in Domain1 AND col2 in Domain2 AND ... colN in DomainN | |
str LIKE ‘%fo_o%' | Find substrings |