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.