PARTITION GROUP BY does not work for unsegmented tables

Sergey_Cherepan_1

Hi,

I found set of tables in prod with very high number of ROS. More checking show that user created unsegmented tables partitioned by date.

My first reaction was to add PARTITION GROUP BY and partition_table. I found that PARTITION GROUP BY does not have any effect on ROS for unsegmented tables.

Not sure if it is by design, I have not found any reference in docs.

If you think this is a defect, please file ticket to development. At least should be mentioned in docs.

(I addressed problem by converting tables to segmented).

Thank you
Sergey

SruthiA

could you please share me the error and sample query?

Sergey_Cherepan_1

Hi @SruthiA

There is no error and no query involved. When unsegmented table is partitioned, and have group by, I still see individual ROS per date.

I switched all unsegmented partitioned tables to segmented. Unsegmented partitioned tables is a bad practice, even if group by would work. There are so many drawbacks in those tables that not even worth discussing.

Vertica in docs correctly pointed out that partitioning should be done only for large tables.

I think Vertica just need to mention in docs that partitioning for unsegmented tables is a bad practice, and partitioning group by is not supported.

Sergey

Bryan_H

Hi, could you share the table DDL? Did you mergeout after data load? Consider the following:
d2=> create table forumpart (i int not null, v varchar) unsegmented all nodes partition by i group by (i % 10);
I loaded many rows with random "i" and saw many containers during load. After mergeout, there were only 10 ROS containers as expected.
However, I see you used DATE, so I wonder if that is causing an issue.

Sergey_Cherepan_1

@Bryan_H
The way I saw problem:
User had fairly small unsegmented daily partitioned table with a couple of years of data. I checked ROS count on table.
I changed partitioned, added GROUP BY calendar_hierarchy_day(2,2) and run partition_table. I checked ROS count on table and it did not changed.
It appears, you need to manually run do_tm_task('mergeout') on table. Then, daily ROS start merging into monthly.
Works as expected. Sorry for false alert.
Sergey