After upgrading the Postgres Server from Version 15 to Version 17 running on RHEL 9, few of the SQLs changed the plan and running pretty slow. I am basically an Oracle DBA and thinking to perform the follow, but want to know what analyze option are best to calculate the stats.
Beside above 2 options, if you have any further recommendations that would be highly appreciated.
Read instructions in the release notes for every major version you traversed. There is always a chapter with essential notes. In your case:
The rest of the release notes may be of interest, too.
You already mentioned "gathering statistics". Make sure to run a global ANALYZE in the database, since statistics for the optimizer are not transferred to the new version by pg_upgrade.
As privileged user connected to the database in question:
ANALYZE;
Or with vacuumdb from the shell - one of:
vacuumdb [connection-option...] --analyze-only
vacuumdb [connection-option...] --analyze
Rebuilding indexes is not necessary unless one of these chapter explicitly instructs to do so. (But if your indexes are bloated, REINDEX won't hurt.)