The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!


relireli Vertica Customer

I declre this partition on table:

        PARTITION BY   date(ts at time zone 'UTC')::date 
        GROUP BY (
        CASE WHEN DATEDIFF('MONTH', date(ts at time zone 'UTC')::date, NOW()::TIMESTAMPTZ(6)) >= 12 
               THEN DATE_TRUNC('MONTH', date(ts at time zone 'UTC')::date)
             ELSE date(ts at time zone 'UTC')::date END);

During the day Vertica inserts to the table, data with time ts.
At the end of each business day I will call ANALYZE_STATISTICS_PARTITION and collect statistics on all data
what is the best query to run:
SELECT ANALYZE_STATISTICS_PARTITION('public.T2', minvalue, maxvalue);
when max and min are :

select projection_name, min(partition_key), max(partition_key) from partitions
group by projection_name

Best Answers

  • Answer ✓

    Optimizer is not going to do partition pruning, if partition is on expression.
    There is a performance benefits on adding date column and partitioning on column directly.
    That, of course, come at price of increased license usage for data size.

  • Vertica_CurtisVertica_Curtis Employee
    Answer ✓

    I'm not sure Vertica cares which command you run. It's going to identify applicable partitions, and run stats on those partitions. So, I suspect they are functionally equivalent.

    I'm not sure what you're argument is there, Sergey - the optimizer will do partition pruning if you're doing a filter on the partitioned expression. The optimizer even does partition pruning even if it's not. If I partition on "state", and do a query lookup by zip-code, the optimizer can determine that certain zip codes don't exist in certain states, and can prune those states from its target set. It uses statistics min/max values at the ROS level to know what to prune.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file