Re: Slow timestamp query after upgrading from Pg13 to Pg16

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

Thanks.

- Can you show the index definitions on that table?

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

Yours,
Laurenz Albe