From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Osborne <david@qcode.co.uk>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow performance after restoring a dump
In-reply-to: 
 <CAKmpXCduMKf=iO=YjKQcKgfd9V4jS-aKD_JJfvMTYEhQOv6ASQ@mail.gmail.com>
References: 
 <CAKmpXCfZUyTwOT3m0HF+uAGbjHt9GHHvhP4jty6QJQLU4Ag8Dw@mail.gmail.com>
 <31890.1521473700@sss.pgh.pa.us>
 <CAKmpXCduMKf=iO=YjKQcKgfd9V4jS-aKD_JJfvMTYEhQOv6ASQ@mail.gmail.com>
Comments: In-reply-to David Osborne <david@qcode.co.uk>
	message dated "Mon, 19 Mar 2018 15:43:47 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1564.1521476520.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 19 Mar 2018 12:22:00 -0400
Message-ID: <1565.1521476520@sss.pgh.pa.us>
Precedence: bulk

David Osborne <david@qcode.co.uk> writes:
> Hi, yes I've run "analyse" against the newly restored database. Should t=
hat
> be enough?

My apologies, you did say that further down in the original message.
It looks like the core of the problem is the poor rowcount estimation
here:

                     ->  Bitmap Index Scan on stock_trans_product_idx (cos=
t=3D0.00..31.42 rows=3D1465 width=3D0) (actual time=3D0.009..0.009 rows=3D=
0 loops=3D1)
                           Index Cond: (product_id =3D 2466420)
                           Buffers: shared hit=3D3

You might be able to improve that by raising the statistics target
for stock_trans.product_id.  I'm not sure why you weren't getting
bitten by the same issue in 9.1; but the cost estimates aren't
that far apart for the two plans, so maybe you were just lucky ...

			regards, tom lane