Refresh Statistics

Moderator: NorbertKrupa

Post Reply
User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

Refresh Statistics

Post by bud » Mon Nov 04, 2013 3:40 pm

Hi,

Is there a best practice for how often table statistics need to be refreshed? How can I tell which tables have out of date statistics?

Thanks!
Bud Anderson

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Refresh Statistics

Post by nnani » Tue Nov 05, 2013 8:23 am

#bud,

For finding if the table has stale statistics, you can check the explain plan of the query referring the table.
The Explain Plan mentions, if there are NO STATISTICS or STALE STATISTICS.

Besides, you can check if the Projections have stastistics or no using this query

Code: Select all

select anchor_table_name,has_statistics from projections;
And for checking when was the last time the statistics were updated

Code: Select all

SELECT projection_column_name, statistics_type,
statistics_updated_timestamp
FROM PROJECTION_COLUMNS;
In General the table Stats need to updated after every bulk loading of table and before a complex query is executed on the table.

Hope this helps.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Refresh Statistics

Post by scutter » Tue Nov 05, 2013 11:16 pm

The QUERY_EVENTS table also includes so events related to stale/missing statistics that help you to see that a query was planned without up to date statistics. Workload Analyzer also helps you to identify statistics that need updating. But best is to incorporate the collection of stats into your ETL processes.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

Re: Refresh Statistics

Post by bud » Wed Nov 06, 2013 12:44 pm

nnani/scutter,

Thanks for the help! I'll incorporate a daily check that looks for stale and no statistics and run the analyze_statistics funcntion on tables I load during the ETL.
Bud Anderson

Post Reply

Return to “New to Vertica Database Administration”