Analyze Statistics - HAS_STATISTICS returning false

We are running analyze statistics on all objects every weekend using select analyze_statistics(''); but then when they look up at the Projections table, HAS_STATISTICS column, it is returning false.
 
When I look up the documentation for HAS_STATISTICS column on Projections table, it says
 
"This column returns true only when all non-epoch columns for a table have full statistics. Otherwise column returns false"
 
Can you help me understand what does non-epoch column mean and would there be any reason by statistics may not be collected for all the tables?
 
Note: None of these tables are empty

Comments

  • Hi ,
    Looks like something was failed with the execution of your statistics collections .
    I advice you to take a look into your error_messages table see eg: query
    select * from error_messages orderby event_timestamp desc;
    Or vertica.log file .


    Regarding this statement "This column returns true only when all non-epoch columns for a table have full statistics. Otherwise column returns false"


    When you create a table (projection ) in vertica , vertica automatically add internal column named by "epoch" to each projection  , the epoch column values are being set internally by vertica at a commit time , it basically include the system commit number ( in Oracle thay call it SCN ) and it mainly being used by vertica for managing the on going activities of the database .

     

    I hope you find it useful

     

    Thanks 

  • Thanks for the reply. I did not see anything in the error_messages which is relevent to this. What I observed is that for the tables, that has_statistics is showing up as false, when I look up under projections_columns table, stats that seems to be applied are ROW_COUNT. But FULL stats always has the precedence over ROW_COUNT.

     

    However, they are not truncating the table as it got history data. I was more interested to see if there is any way to find out Analyze_statistics('') function ran successfully and if yes, the list of projections for which stats got updated. All I could see is the statistics updated timestamp in the projection_columns table.

     

     

     

     

  • Select get_projections('<schema_name.table_name>'); to see the list of projections for that table and if the stats show "yes/no".

     

    Also, when you run Select Analyze_Statistics('<schema_name.table_name>');  It will return 0 after successful completion. 

     

    If your projection_columns table still shows 'ROWCOUNT' for some columns, you may benefit from running 

    Select Analyze_Histogram('<schema_name.table_name.column_name>',100); just for those columns. It will show FULL after that.

     

    Live aggregate projections will not have statistics and they will continue to show 'NONE'. It could be misleading but analyze_statistics is not applicable for those projections. 

     

    Thanks,

    Sashi

     

     

  • I happen to be testing something with statistics and found this thread while searchign for an answer.  I ran many variaions - but to summarize the relevant parts:  What i have found is that if you have 0 rows and take stats they do not show up in projection_columns. (NULL timestamp)  If you have rows, stat, then remove rows and stat again, they convert from "FULL" to 'ROWCOUNT'.  

    For my test cases, i checked the associatd projections and they now show with has_statistics = f.     So, while not sure if this would fit your issue it does seem to match:  if you had rows in a table and stat'ed them, then removed rows and re-stated them, your stats will change to ROWCOUNT and your projection has_statistics will change to 'f'.    hth

    -paul

Leave a Comment

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