Message-ID: <53f4c3238460ea2b6e8677b4419bacd6a4445e83.camel@cybertec.at>
Subject: Re: Slow timestamp query after upgrading from Pg13 to Pg16
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Simon Windsor <simon.windsor@cornfield.me.uk>, 
	pgsql-general@postgresql.org
Date: Tue, 08 Apr 2025 08:40:11 +0200
In-Reply-To: <692a315e-6dd8-4194-8c7f-c45425852e77@cornfield.me.uk>
References: <27b2975b-c078-45cf-815d-eddbedbda153@cornfield.me.uk>
	 <092224fb0376bbc6a5f777199dde3cb9cd1ebf99.camel@cybertec.at>
	 <692a315e-6dd8-4194-8c7f-c45425852e77@cornfield.me.uk>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/53f4c3238460ea2b6e8677b4419bacd6a4445e83.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-04-07 at 20:25 +0100, Simon Windsor wrote:
> We used pg_dump|pg_restore to migrate the data.
>=20
> The full explain plan is at https://explain.depesz.com/s/742M. The SQL
>=20
> explain (analyze, buffers) select count(*) from consignments where=20
> (req_status_tstamp >=3D '2025-03-28 00:00'::timestamp and=20
> req_status_tstamp <=3D '2025-03-28 01:00'::timestamp);
>=20
> takes 2-3s with the old Pg13 DB, and over a minute with Pg16
>=20
> After spending many hours looking at DB settings and Statistic settings=
=20
> I am at a loss/

Thanks.

- Can you show the index definitions on that table?

- If you "SET enable_seqscan =3D off;" in an interactive session, PostgreSQ=
L
  should choose an index scan if possible.  Can you show the
  EXPLAIN (ANALYZE, BUFFERS) output for that execution?

Yours,
Laurenz Albe