Performance Issue after upgrade from 9 to 11

On 1/29/20 9:39 AM, Perumal Raj wrote:

Hi All,

We have recently upgraded postgres from 9.2 to 11.6 and started seeing
performance issue immediately and  able to fix the performance issue
after disabling parameter: enable_seqscan.

Question :
Should i keep the above parameter always disabled ? If not why the
behavior changed in Higher version ?

Without an explain analyze of a representative query it would be hard to
say.

Also the schema of the the tables involved would be helpful.

Note:
Table ANALYZE completed as part of Upgrade activity.

Was this on a single table or all tables?

Thanks
Raj

--
Adrian Klaver
adrian.klaver@aklaver.com

Perumal Raj <perucinci@gmail.com> writes:

We have recently upgraded postgres from 9.2 to 11.6 and started seeing
performance issue immediately and able to fix the performance issue after
disabling parameter: enable_seqscan.
Question :
Should i keep the above parameter always disabled ? If not why the behavior
changed in Higher version ?

This is unanswerable with the amount of information you've given.
Yes, turning off enable_seqscan is a bad idea in general, but why
you got a worse plan without that requires details.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

regards, tom lane

Hi Tom /Adrian,

Issue is not specific to a table or particular Query. Also there is no
change in DB parameter after upgrade.

That the only way i can make it most of the the query to run as like before
upgrade.

Note:
Some web reference says , Engine will take some time to adjust until it
runs autovacuum .

On Wed, Jan 29, 2020 at 10:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 2020-01-29 09:39:03 -0800, Perumal Raj wrote:

We have recently upgraded postgres from 9.2 to 11.6 and started seeing
performance issue immediately and  able to fix the performance issue after
disabling parameter: enable_seqscan.

How did you upgrade?

If your upgrade involved a dump and restore, you should invoke ANALYZE
for each database (I think autovacuum will analyze all tables
eventually, but takes its time).

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

Hi Peter,

I strongly i agree,

I have used pg_upgrade which runs "analyze_new_cluster.sh" as post upgrade
activity across all DB on cluster.
Also, I have executed manual vacuum on all individual tables. However the
behavior is same until i disable the above said parameter.

Regards,
Raj

On Wed, Jan 29, 2020 at 2:33 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 1/29/20 1:58 PM, Perumal Raj wrote:

Hi Tom /Adrian,

Issue is not specific to a table or particular Query. Also there is no
change in DB parameter after upgrade.

You obviously are seeing some difference, so pick a query and do an
EXPLAIN ANALYZE on it. Without some actual performance data this issue
is not going to be solved.

That the only way i can make it most of the the query to run as like
before upgrade.

Note:
Some web reference says , Engine will take some time to adjust until it
runs autovacuum .

The above assumes you have not done a manual ANALYZE on the database, as
ANALYZE is done as part of autovacuum.

On Wed, Jan 29, 2020 at 10:22 AM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

Perumal Raj <perucinci@gmail.com <mailto:perucinci@gmail.com>> writes:

We have recently upgraded postgres from 9.2 to 11.6 and started

seeing

performance issue immediately and  able to fix the performance

issue after

disabling parameter: enable_seqscan.
Question :
Should i keep the above parameter always disabled ? If not why

the behavior

changed in Higher version ?

This is unanswerable with the amount of information you've given.
Yes, turning off enable_seqscan is a bad idea in general, but why
you got a worse plan without that requires details.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

                        regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Perumal Raj <perucinci@gmail.com> writes:

I have used pg_upgrade which runs "analyze_new_cluster.sh" as post upgrade
activity across all DB on cluster.

pg_upgrade itself won't run that script, it only creates it for you
to run.

Also, I have executed manual vacuum on all individual tables. However the
behavior is same until i disable the above said parameter.

Did those manual vacuums include an "analyze" option?

If you in fact haven't got analyze results on your tables, that might
explain your problems. autovacuum would eventually rectify the situation
for larger tables, but it might never get around to small seldom-updated
tables.

If that's not it, I again direct your attention to the
Slow_Query_Questions wiki page. That summarizes a lot of past experience
with solving performance problems, and you're not endearing yourself to
potential helpers by refusing to follow the process.

regards, tom lane