Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Best way to have Partition "Pruned storages" on a query

Hi ,

My partition key on the table is Year Mont WeekInAMonth something like 
20130902 ,Partition expresion on the create table is   - > PARTITION BY DATE_PART('year', bpm_application_performance_1.timestamp_utc)* 100+ date_part('month', bpm_application_performance_1.timestamp_utc)*10+ CEILING(DATE_PART('day',bpm_application_performance_1.timestamp_utc)/ 7) 

I find Vertica  able to Pruned the partition only after i put the same experation of the PARTITION BY  in my query (only after i add this to the query -> and      DATE_PART('year', bpm_application_performance.timestamp_utc)* 100+ date_part('month', bpm_application_performance.timestamp_utc)*10+ CEILING(DATE_PART('day',bpm_application_performance.timestamp_utc)/ 7)     >=DATE_PART('year', to_timestamp(1378067838))* 100+ date_part('month', to_timestamp(1378067838))*10+ CEILING(DATE_PART('day',to_timestamp(1378067838))/ 7)      and  DATE_PART('year', bpm_application_performance.timestamp_utc)* 100+ date_part('month', bpm_application_performance.timestamp_utc)*10+ CEILING(DATE_PART('day',bpm_application_performance.timestamp_utc)/ 7) <=      DATE_PART('year', to_timestamp(1378067838+3600))* 100+ date_part('month', to_timestamp(1378067838+3600))*10+ CEILING(DATE_PART('day',to_timestamp(1378067838+3600))/ 7)


Are this is the only way to have  pruning  for such partition key ?  looking in the explain plan its looks like it required extra resources !


Thanks 

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.