Re: Slow timestamp query after upgrading from Pg13 to Pg16

On 4/7/25 12:25, Simon Windsor wrote:
> Hi
> 
> We used pg_dump|pg_restore to migrate the data.

Did you do an ANALYZE on the Postgres 16 instance after the pg_restore?

> 
> 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

Do you have an EXPLAIN ANALYZE for the Postgres 13 case?

> 
> After spending many hours looking at DB settings and Statistic settings 
> I am at a loss/
> 
> Simon
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com