Thanks for the insight on the internals. Regarding your questions:
On Tue, 31 Jan 2023, Tom Lane wrote:
Do you get the same 10 rows when you repeat the command?
Yes. Just tested with both cold and hot caches. The first 10 rows are exactly the same, either they return slowly or immediately.
If turning synchronize_seqscans off changes the behavior, that'd be a good clue that this is the right theory.
Turning it off makes the query slow no matter how many times I re-run it. The system is doing lots of read I/O with both hot and cold caches. Here is the EXPLAIN output from the hot cache run (that previously had only 14 hits and no reads): Limit (cost=0.00..0.29 rows=10 width=42) (actual time=620510.813..620510.821 rows=10 loops=1) Output: run_n, test_name_n, workitem_n, started_on, duration_ms, test_result_n, test_executable_n, test_function_n, test_datatag_n Buffers: shared hit=64 read=2334462 I/O Timings: shared/local read=567846.559 -> Seq Scan on public.test_runs_raw (cost=0.00..9250235.80 rows=317603680 width=42) (actual time=620510.800..620510.804 rows=10 loops=1) Output: run_n, test_name_n, workitem_n, started_on, duration_ms, test_result_n, test_executable_n, test_function_n, test_datatag_n Buffers: shared hit=64 read=2334462 I/O Timings: shared/local read=567846.559 Settings: effective_cache_size = '2GB', max_parallel_workers_per_gather = '0', work_mem = '64MB' Planning Time: 0.099 ms Execution Time: 620510.855 ms After reading the docs, I'm surprised this setting affects my case given that I have no parallelism in my setup.
As for a real fix, it might be time for a VACUUM FULL or CLUSTER on that table.
Regarding CLUSTER, would it help with a seqscan on a bloated table? Furthermore, given that the table is expected to grow every day by a few million rows, do you suggest running CLUSTER every night? Will postgres remember that the rows up to N are clustered, even after appending more rows? Dimitris