Обсуждение: 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

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!"

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