All‑AMP Tactical Queries and Partitioned Tables
The major benefit that can be realized for all-AMP tactical queries running against partitioned tables is row and column partition elimination. Rather than scanning an entire table, it is possible to scan a smaller subset of rows if the query specifies the partitioning key value for the table or a subset of the columns when column partitioned.
Avoid adjusting the granularity of partitions more finely than is warranted by the majority of the queries that run against the table. For example, if users never access data with a granularity smaller than a monthly range, there is no additional performance advantage to be gained by defining partitions that have a smaller range than one month.
For all-AMP tactical queries, you should include the partitioning columns as a constraint in the WHERE clause of your queries. If a query joins row-partitioned tables that have identical row partitioning, you can enhance join performance still further by including an additional equality constraint on the partitioning columns of the two tables.