Обсуждение: unanalyze a foreign table
I did a manual ANALYZE of a foreign table, to see if it would make a troublesome query better. It did, but it also made some other queries that were previously fine to become far worse. Is there a way to undo the analyze? I can muck around in the catalogs like below, but seems really grotty.
delete from pg_statistic where starelid=418911;
The other option seems to be doing a `drop foreign table ... cascade`, but then recreating all the cascaded drops is quite annoying and error prone.
I currently solved it by re-cloning my performance testing server from upstream, but that also is not very convenient. Is directly manipulating the catalogs really the best way?
Cheers,
Jeff
I do not know of way to undo an analyze once its committed. I do not know the danger in deleting an entry in pg_statistic
What you can do in the future is make copy of the Statics for this table, analyze, if it negatively affect results put the copy back.
Another option is to do
begin ;
ANALYZE my_problem_table ;
explain select my_problem_query;
rollback ;
ANALYZE my_problem_table ;
explain select my_problem_query;
rollback ;
Foreign tables are not be default analyzed so the statistics should have been empty or no entry, unless it was previously analyzed.
On Sun, Dec 22, 2019 at 2:22 PM Jeff Janes <jeff.janes@gmail.com> wrote:
I did a manual ANALYZE of a foreign table, to see if it would make a troublesome query better. It did, but it also made some other queries that were previously fine to become far worse. Is there a way to undo the analyze? I can muck around in the catalogs like below, but seems really grotty.delete from pg_statistic where starelid=418911;The other option seems to be doing a `drop foreign table ... cascade`, but then recreating all the cascaded drops is quite annoying and error prone.I currently solved it by re-cloning my performance testing server from upstream, but that also is not very convenient. Is directly manipulating the catalogs really the best way?Cheers,Jeff