How does Vertica decide that statistics are stale or invalid?

Possible reasons:
1) VER-68339 - Statistics lost after one node crashed
2) If a query includes columns with no statistics the query optimizer regards the
statistics as incomplete for that query and ignores them in its plan.
3) Data is bulk loaded for the first time.
4) A new projection is refreshed.
5) SWAP partition
6) A new column is added to the table.
7) It is a bug
Did you try select clear_caches();
if it doesn’t help try SELECT DROP_STATISTICS('schema.table_name' , 'all' );

Also be aware that if you run a query with a sub-select, the sub-select will show up in the explain as having no statistics, because it's not actually a table.

I don't know how Vertica computes FULL statistics. If you have a table with values of 1-100, and you run stats on it, the stats will acknowledge the range of 1 to 100. But if you add a single record (101), and then query against that, query_events will track that you've queried a table with a join condition out of range of that statistic. This is an unavoidable situation, and shouldn't be viewed as a solvable "problem".

But this entire conversation strikes me as a mask for something else. Like, this particular customer might be having performance issues (likely caused by poor segmentation, or bad projection design), and has decided to blame it on statistics.

I've rarely seen statistics ever have any positive effect on query performance. It certainly CAN - and I have seen it have a positive effect in certain cases, but generally, I don't expect it to have an effect. If it does, then great. I've also got a case open where statistics actually caused my queries to run SLOWER. So, just putting stats on a query isn't a magic bullet.

It's possible your error is a bug, or the existing stats on the table are corrupted in some way. I'd try dropping the stats for that table to see if it fixes the issue.

Thanks @mosheg !
a followup question if I may
2) "If a query includes columns with no statistics the query optimizer regards the
statistics as incomplete for that query and ignores them in its plan."
==> does it mean that vertica will mark these statistics as stale and the tuple mover (or something else?) later on will update the metadata of the projections statistics_type from FULL to ROWCOUNT ?

@Vertica_Curtis
Thanks
Here it looks like our customer is "losing" somehow statistics on certain tables, for mysterious reasons, still not sure why, hence my questions on the theory of statistics (I want to understand what happens under the hood and so does my customer )
if there is a bug we need to identify it..
They monitor projection_columns system table and the column statistics_type.
They update their statistics after every DML so expect the stats to be always up to date, as they should.
We just had a case two days ago where there was a huge performance degradation on certain queries (10 times more memory for instance and huge impact for all the other users) overnight, we traced it back to a small table used by all the mentioned queries with no statistics, in its ELT daily job there was an analyze_statistics at the end, but the stats weren't really updated (statistics_type='ROWCOUNT' and in the logs the weird message I posted)
When the same daily job the night after was executed every thing was back to order.
This is not the first time we've seen this behaviour on this customer's clusters, ie, the projections metadata say that there is only rowcount, but when we check the query requests we find that an analyze_statistics run successfully.
I've had many cases where statistics have huge performance impact especially on complex queries, it's definitely not always the problem solver, but still a crucial part.
When we have a performance degradation on known queries, there is a big chance it's do to the absence of statistics.
So we're very careful when it comes to statistics and so is the customer.
Kind regards,

Consider an example where a table has statistics as shown below..
select p.projection_name || '.' || p.anchor_table_name as Table,
case when p.has_statistics then 'yes' else 'no' end as has_statistics,
case when p.is_aggregate_projection then 'yes' else 'no' end as LAP
from projections p inner join projection_storage ps on p.projection_id = ps.projection_id
WHERE p.anchor_table_name = 'my_tablex2'
group by 1,2,3 order by 1 ;
Table | my_tablex2_super.my_tablex2
has_statistics | yes
LAP | no

Now we add a new column to the table,
so the table lose its statistics as shown below..
Table | my_tablex2_super.my_tablex2
has_statistics | no
LAP | no

In another example, after ANALYZE_STATISTICS, the table has statistics.
However, the query optimizer ignores the (existing) statistics
because the query include a field (epoch) with no statistics.
Hence the Explain shows no statistics..
+-SELECT LIMIT 10 [Cost: 23K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)
| +---> SORT [TOPK] [Cost: 23K, Rows: 101K (NO STATISTICS)] (PATH ID: 1)
| | +---> STORAGE ACCESS for my_tablex2 [Cost: 3K, Rows: 101K (NO STATISTICS)] (PATH ID: 2)

Thanks again @mosheg
I was also trying to understand when Analyze_Row_Count (run by Tuple Mover) overwrites FULL statistics,
In the documentation there are two parameters related to Analyze_Row_Count:
AnalyzeRowCountInterval
Specifies how often Vertica checks the number of projection rows and whether the threshold set by **ARCCommitPercentage ** has been crossed.

ARCCommitPercentage
Sets the threshold percentage of WOS to ROS rows, which determines when to aggregate projection row counts and commit the result to the catalog. Vertica performs this action when the WOS to ROS percentage exceeds this setting.
Default = 3%
I don't fully understand the description of ARCCommitPercentage,
But from my tests, when we add more than 3% of data to a table, and the analyze_row_count is invoked by TM, we lose the histogram and statistics go from FULL to ROWCOUNT (projection_columns table)
(I also discovered that in 9.2 AnalyzeRowCountInterval went from 60 seconds in previous versions to a full day ! )

Regarding the message [Analyze_Stats] Sample is empty, computeHistogram will not be run (NO stats collected) for this column , I looked at the customer's logs, there was a node crash, and many analyze_statistics run between the node DOWN and the node RECOVER returned that message, saying that the analyze_statistics didn't compute the histogram, however the ANALYZE_STATISTICS command was successful (returned 0).
I'm opening a support case for this, maybe it come from the same bug you mentioned VER-68339

KR

AnalyzeRowCount used to cause a lot of problems. Support would routinely advised people to decrease the frequency of it from 60 seconds to an hour, or more. Running it every minute seems pretty ludicrous.

Yeah, glad we changed the default in Vertica 9.2!

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.2.1-4
(1 row)

dbadmin=> SELECT DISTINCT default_value AS default_value_secs, default_value / 60 / 60/ 24 default_value_days, description FROM vs_configuration_parameters WHERE parameter_name = 'AnalyzeRowCountInterval';
 default_value_secs | default_value_days |                             description
--------------------+--------------------+---------------------------------------------------------------------
 86400              |                  1 | Interval between Tuple Mover row count statistics updates (seconds)
(1 row)