drop projections, update query performance

Try to define HistoryRetentionTime.
Im not sure that it is  issue of HistoryRetentionTime, but looks very similar.

Important note:

In Vertica 4.1, the default for the HistoryRetentionTime configuration parameter changed to 0, which means that Vertica only keeps historical data when nodes are down. This new setting effectively prevents the use of the Administration Tools 'Roll Back Database to Last Good Epoch' option because the AHM remains close to the current epoch and a rollback is not permitted to an epoch prior to the AHM. If you rely on the Roll Back option to remove recently loaded data, consider setting a day-wide window for removing loaded data; for example:

=> SELECT SET_CONFIG_PARAMETER ('HistoryRetentionTime', '86400');

[DUPLICATE]
https://community.vertica.com/vertica/topics/get_last_good_epoch_not_advancing_so_cant_drop_old_proj...

Try the below:

Verify the output refresh_epoch with current_epoch, they should be uptodate - rather same

dbadmin=> select current_epoch,last_good_epoch, ahm_epoch, refresh_epoch from system;

if not then run
dbadmin=>select start_refresh();

probably projections are not refreshed properly.

Daniel - I think you have misinterpreted the purpose of HistoryRententionTime.  That configuration parameter controls how far back data is kept before it is eligible for being purged.  By default, AHM stays pretty current, so that a ROS container that is merged is also likely to purge recently-deleted records in the merged containers.  If you set HistoryRetentionTime to be longer - let's say 6 hours, then AHM will stay 6 hours back, maintaining 6 hours of history, and deleted records won't be purged for at least 6 hours.  A stale AHM also means that deleted records won't be removed when a table or projection is purged.  I wouldn't change this setting without fully understanding the consequences since large numbers of deleted records can negatively impact performance.

Prasanta pointed towards unrefreshed projections, which is the likeliest cause of AHM being held back.  Check the projections table for projections that are not is_up_to_date.  You may want to drop them, or refresh them, once you see the list.

Thank you for your help.
Our DBA team worked with your support.
After a few hours they were able to set the ahm_time to now
and then the drop of the projections was no problem.