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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Dimitrios Apostolou <jimis(at)gmx(dot)net>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Date: 2023-01-31 15:20:36
Message-ID: CAKFQuwah8Zbn52oY6Mz6saydH6NUjbX+bmsh_kH+7BbgmwUy8Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou <jimis(at)gmx(dot)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.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 you most likely have a large
bloating problem going on. Seems like it is time to vacuum full.

David J.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-01-31 15:28:21 Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Previous Message Dimitrios Apostolou 2023-01-31 15:16:16 Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches