Thread: Performance Issue after upgrade from 9 to 11

Performance Issue after upgrade from 9 to 11

From
Perumal Raj
Date:
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 ?

Note:
Table ANALYZE completed as part of Upgrade activity.

Thanks
Raj

Re: Performance Issue after upgrade from 9 to 11

From
Adrian Klaver
Date:
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



Re: Performance Issue after upgrade from 9 to 11

From
Tom Lane
Date:
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



Re: Performance Issue after upgrade from 9 to 11

From
Perumal Raj
Date:
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:
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

Re: Performance Issue after upgrade from 9 to 11

From
"Peter J. Holzer"
Date:
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!"

Attachment

Re: Performance Issue after upgrade from 9 to 11

From
Perumal Raj
Date:
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 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!"

Re: Performance Issue after upgrade from 9 to 11

From
Adrian Klaver
Date:
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



Re: Performance Issue after upgrade from 9 to 11

From
Tom Lane
Date:
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