Query much slower on 9.6.5 than on 9.3.5

From: Rob Northcott <Rob(dot)Northcott(at)compilator(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Query much slower on 9.6.5 than on 9.3.5
Date: 2019-04-05 08:43:18
Message-ID: PR2PR08MB48749A44649B6EDAB691F00F9B510@PR2PR08MB4874.eurprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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<http://www.compilator.com/>

[Asset 2(at)2x]<http://www.compilator.com/>

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2019-04-05 09:12:37 Re: Query much slower on 9.6.5 than on 9.3.5
Previous Message SwissPUG Info 2019-04-05 07:15:01 Swiss PGDay 2019 - CfP and registration are open - reminder