On Tue, 31 Jan 2023, David G. Johnston wrote:
On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou <ji...@gmx.net> wrote: -> Seq Scan on public.test_runs_raw (cost=0.00..9250235.80 rows=317603680 width=42) (actual time=745910.672..745910.677 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 read=2334526 I/O Timings: shared/local read=691137.029The system has to return 10 live rows to you. If it needs to search through that many buffers to find 10 live rows you most likely have a large bloating problem going on. Seems like it is time to vacuum full.
I looked up on how to measure bloat, so I run the query found at [1]. [1] https://wiki.postgresql.org/wiki/Show_database_bloat The first two rows show huge bloat on the two indices of this table: ... ORDER BY wastedbytes DESC LIMIT 2; current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes ------------------+------------+---------------+--------+-------------+------------------------------+--------+-------------- coin | public | test_runs_raw | 1.8 | 21742305280 | test_runs_raw_pkey | 1.0 | 0 coin | public | test_runs_raw | 1.8 | 21742305280 | test_runs_raw_idx_workitem_n | 0.3 | 0 (2 rows) Is this bloat even affecting queries that do not use the index? It seems I have to add VACUUM FULL to nightly maintainance. I had run some schema restructuring (several ADD COLUMN followed by UPDATE SET on all rows) some days ago, and I was not aware this degraded the table. Thanks for the useful info! Dimitris
