PostgreSQL 14.1. We read all data from a simple table when our application starts. This used to return the first row very quickly, but now - for no reason I can think of - we have a very noticeable delay before the first row is returned. We noticed this because original application uses Npgsql, which has a default timeout of 20 seconds, and we got timeouts all the time on a large table.
I can reproduce this using psql however, with a query as simple as
psql -d mydb -c "select * from event_1"
With "EXPLAIN (ANALYZE, BUFFERS)" with track_io_timing = on:
Seq Scan on event_1 (cost=0.00..318003.14 rows=17601214 width=37) (actual time=0.242..2479.949 rows=17698953 loops=1) Buffers: shared read=141991 I/O Timings: read=1396.307 Planning: Buffers: shared hit=84 read=10 I/O Timings: read=0.686 Planning Time: 1.507 ms Execution Time: 3022.822 ms
With 17 million rows the delay is about 25 seconds and with >400M rows it's 3.5 minutes. I'm sure this did not happen before, because we read that 400M row table with the default timeout of 20 seconds in Npgsql many times.
How do I figure out why this is happening now?
I did make a schema change to the DB (re-ordering columns) before this happened, but with that change reverted (DB re-created with the old schema) the problem still occurs. It occurs on two separate DB servers, one of which is doing nothing else and has plenty of RAM.