Thread: Query much slower on 9.6.5 than on 9.3.5

Query much slower on 9.6.5 than on 9.3.5

From
Rob Northcott
Date:

I’ve had a couple of customers complaining of slow searches and doing some testing last night it seems to be much slower on the live server than on my test setup.

It’s quite a messy query built up by the search code, with lots of joins and subqueries.

I’ve downloaded a backup of the customer’s live database to test, so I’m running the same query on the same data, just two different servers.

On the local test server (PSQL 9.3.5 running on an old Core2 Duo PC) it takes around 200ms to run the query.

On the live server (PSQL 9.6.5 on virtual server with 4 cores) it takes 20 seconds to run the same query.

 

Looking at the explain analyse, the two servers are using quite different optimisation plans, but I can’t find any differences in the settings.

 

Is there anything obvious I should look at that may be different between 9.3 and 9.6?

 

If not, would it help if I post the analyse output on here? (can we post attachments to the group or should it just be text in the email?)

 

Many thanks for any hints

 

Med vänlig hälsning / Best Regards

 

Rob Northcott

Software Developer (UK Office, formerly TEAM Systems)

 

Phone   +44 1752 712052

 

Compilator AB

Södergatan 22

SE-211 34 Malmö

Sweden

www.compilator.com

 

Asset 2@2x

 

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL AND IS THUS FOR USE ONLY BY THE INTENDED RECIPIENT. IF YOU RECEIVED THIS IN ERROR, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL AND ITS ATTACHMENTS FROM ALL COMPUTERS.

 

Attachment

Re: Query much slower on 9.6.5 than on 9.3.5

From
Ron
Date:
On 4/5/19 3:43 AM, Rob Northcott wrote:

I’ve had a couple of customers complaining of slow searches and doing some testing last night it seems to be much slower on the live server than on my test setup.

It’s quite a messy query built up by the search code, with lots of joins and subqueries.

I’ve downloaded a backup of the customer’s live database to test, so I’m running the same query on the same data, just two different servers.

On the local test server (PSQL 9.3.5 running on an old Core2 Duo PC) it takes around 200ms to run the query.

On the live server (PSQL 9.6.5 on virtual server with 4 cores) it takes 20 seconds to run the same query.

 

Looking at the explain analyse, the two servers are using quite different optimisation plans, but I can’t find any differences in the settings.

 

Is there anything obvious I should look at that may be different between 9.3 and 9.6?

 

If not, would it help if I post the analyse output on here? (can we post attachments to the group or should it just be text in the email?)

 

Many thanks for any hints


I'll get the obvious first question out of the way, so that no one else has to ask: have you analyzed the 9.6 database?  If not, do that first.

--
Angular momentum makes the world go 'round.

RE: Query much slower on 9.6.5 than on 9.3.5

From
Rob Northcott
Date:

Hi Ron,

 

Thanks for that.  I did just run analyse and vacuum on the live database before I saw your message and it has sorted it out.

Do you think the analyse on its own would have cured it, or would it have been the vacuum? (vacuum took a long time).

Perhaps I should schedule a vacuum to run periodically to stop this happening again – I didn’t think it would be necessary because these aren’t particularly big or heavily-used databases, but it seems I was wrong.

 

Also, my assumption that downloading a backup and restoring it locally would replicate the problem would seem to be wrong.  Presumably the backup removes any dead stuff, so backup/restore has the same effect as a vacuum?

 

Anyway, looks like I was panicking prematurely, but thanks for the help anyway 😊

 

Rob

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: 05 April 2019 10:13
To: pgsql-general@lists.postgresql.org
Subject: Re: Query much slower on 9.6.5 than on 9.3.5

 

On 4/5/19 3:43 AM, Rob Northcott wrote:

I’ve had a couple of customers complaining of slow searches and doing some testing last night it seems to be much slower on the live server than on my test setup.

It’s quite a messy query built up by the search code, with lots of joins and subqueries.

I’ve downloaded a backup of the customer’s live database to test, so I’m running the same query on the same data, just two different servers.

On the local test server (PSQL 9.3.5 running on an old Core2 Duo PC) it takes around 200ms to run the query.

On the live server (PSQL 9.6.5 on virtual server with 4 cores) it takes 20 seconds to run the same query.

 

Looking at the explain analyse, the two servers are using quite different optimisation plans, but I can’t find any differences in the settings.

 

Is there anything obvious I should look at that may be different between 9.3 and 9.6?

 

If not, would it help if I post the analyse output on here? (can we post attachments to the group or should it just be text in the email?)

 

Many thanks for any hints


I'll get the obvious first question out of the way, so that no one else has to ask: have you analyzed the 9.6 database?  If not, do that first.

--
Angular momentum makes the world go 'round.

Re: Query much slower on 9.6.5 than on 9.3.5

From
Ron
Date:
Rob,

pg_dump/restore gets rid of all the dead space, and you should always run an ANALYZE after pg_restore, since pg_restore doesn't populate the statistics tables.

On 4/5/19 4:35 AM, Rob Northcott wrote:

Hi Ron,

 

Thanks for that.  I did just run analyse and vacuum on the live database before I saw your message and it has sorted it out.

Do you think the analyse on its own would have cured it, or would it have been the vacuum? (vacuum took a long time).

Perhaps I should schedule a vacuum to run periodically to stop this happening again – I didn’t think it would be necessary because these aren’t particularly big or heavily-used databases, but it seems I was wrong.

 

Also, my assumption that downloading a backup and restoring it locally would replicate the problem would seem to be wrong.  Presumably the backup removes any dead stuff, so backup/restore has the same effect as a vacuum?

 

Anyway, looks like I was panicking prematurely, but thanks for the help anyway 😊

 

Rob

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: 05 April 2019 10:13
To: pgsql-general@lists.postgresql.org
Subject: Re: Query much slower on 9.6.5 than on 9.3.5

 

On 4/5/19 3:43 AM, Rob Northcott wrote:

I’ve had a couple of customers complaining of slow searches and doing some testing last night it seems to be much slower on the live server than on my test setup.

It’s quite a messy query built up by the search code, with lots of joins and subqueries.

I’ve downloaded a backup of the customer’s live database to test, so I’m running the same query on the same data, just two different servers.

On the local test server (PSQL 9.3.5 running on an old Core2 Duo PC) it takes around 200ms to run the query.

On the live server (PSQL 9.6.5 on virtual server with 4 cores) it takes 20 seconds to run the same query.

 

Looking at the explain analyse, the two servers are using quite different optimisation plans, but I can’t find any differences in the settings.

 

Is there anything obvious I should look at that may be different between 9.3 and 9.6?

 

If not, would it help if I post the analyse output on here? (can we post attachments to the group or should it just be text in the email?)

 

Many thanks for any hints


I'll get the obvious first question out of the way, so that no one else has to ask: have you analyzed the 9.6 database?  If not, do that first.

--
Angular momentum makes the world go 'round.


--
Angular momentum makes the world go 'round.

RE: Query much slower on 9.6.5 than on 9.3.5

From
Rob Northcott
Date:

Ah, fair enough – I’ll get into the habit of doing that then.  Thanks again.

Rob

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: 05 April 2019 11:07
To: pgsql-general@lists.postgresql.org
Subject: Re: Query much slower on 9.6.5 than on 9.3.5

 

Rob,

pg_dump/restore gets rid of all the dead space, and you should always run an ANALYZE after pg_restore, since pg_restore doesn't populate the statistics tables.

On 4/5/19 4:35 AM, Rob Northcott wrote:

Hi Ron,

 

Thanks for that.  I did just run analyse and vacuum on the live database before I saw your message and it has sorted it out.

Do you think the analyse on its own would have cured it, or would it have been the vacuum? (vacuum took a long time).

Perhaps I should schedule a vacuum to run periodically to stop this happening again – I didn’t think it would be necessary because these aren’t particularly big or heavily-used databases, but it seems I was wrong.

 

Also, my assumption that downloading a backup and restoring it locally would replicate the problem would seem to be wrong.  Presumably the backup removes any dead stuff, so backup/restore has the same effect as a vacuum?

 

Anyway, looks like I was panicking prematurely, but thanks for the help anyway 😊

 

Rob

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: 05 April 2019 10:13
To: pgsql-general@lists.postgresql.org
Subject: Re: Query much slower on 9.6.5 than on 9.3.5

 

On 4/5/19 3:43 AM, Rob Northcott wrote:


I’ve had a couple of customers complaining of slow searches and doing some testing last night it seems to be much slower on the live server than on my test setup.

It’s quite a messy query built up by the search code, with lots of joins and subqueries.

I’ve downloaded a backup of the customer’s live database to test, so I’m running the same query on the same data, just two different servers.

On the local test server (PSQL 9.3.5 running on an old Core2 Duo PC) it takes around 200ms to run the query.

On the live server (PSQL 9.6.5 on virtual server with 4 cores) it takes 20 seconds to run the same query.

 

Looking at the explain analyse, the two servers are using quite different optimisation plans, but I can’t find any differences in the settings.

 

Is there anything obvious I should look at that may be different between 9.3 and 9.6?

 

If not, would it help if I post the analyse output on here? (can we post attachments to the group or should it just be text in the email?)

 

Many thanks for any hints


I'll get the obvious first question out of the way, so that no one else has to ask: have you analyzed the 9.6 database?  If not, do that first.

--
Angular momentum makes the world go 'round.

 

--
Angular momentum makes the world go 'round.

Re: Query much slower on 9.6.5 than on 9.3.5

From
Joe Conway
Date:
On 4/5/19 6:23 AM, Rob Northcott wrote:
> Ah, fair enough – I’ll get into the habit of doing that then.  Thanks again.
> 
> Rob

> *From:*Ron <ronljohnsonjr@gmail.com>
> *Sent:* 05 April 2019 11:07
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Query much slower on 9.6.5 than on 9.3.5
> 
> pg_dump/restore gets rid of all the dead space, and you should *always*
> run an ANALYZE after pg_restore, since pg_restore doesn't populate the
> statistics tables.

After the dump/restore cycle I would recommend a "VACUUM FREEZE ANALYZE"
if you can tolerate the time it takes, as this will not only ensure that
the statistics are populated, but it will also get future rewrites of
the rows out of the way all at once (visibility hint bits and freezing
of the tuples).

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development