Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Tue, 31 Jan 2023, David G. Johnston wrote:

> On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
>  
>           ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 rows=317603680 width=42) (actual
time=745910.672..745910.677rows=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.029
>
>
> The system has to return 10 live rows to you.  If it needs to search through that many buffers to find 10 live rows
youmost 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