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(at)gmail(dot)com>
> *Sent:* 05 April 2019 10:13
> *To:* pgsql-general(at)lists(dot)postgresql(dot)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.