Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Christophe Pettus writes: >> On Feb 1, 2023, at 10:45, David G. Johnston >> wrote: >> The system just isn't that intelligent for "sequential scan", instead it >> does literally what the label says, goes through the table one page at a >> time and returns any live rows it finds. > Although th

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

> On Feb 1, 2023, at 10:45, David G. Johnston > wrote: > The system just isn't that intelligent for "sequential scan", instead it does > literally what the label says, goes through the table one page at a time and > returns any live rows it finds. Although this does raise a question: Could

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou wrote: > I have now run simple VACUUM but it didn't change anything, the simple > SELECT is still slow. > > My understanding by reading the docs is that it should reclaim all unused > space, just not return it to the OS. Which is fine by me. Any

VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Thanks everyone for the help. I have now run simple VACUUM but it didn't change anything, the simple SELECT is still slow. My understanding by reading the docs is that it should reclaim all unused space, just not return it to the OS. Which is fine by me. Any idea why it failed to reclaim the spa

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

On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou wrote: > On Tue, 31 Jan 2023, David G. Johnston wrote: > > > > It feels like there is room for improvement here using table statistics > and the visibility map to significantly reduce the number of pages > retrieved that turn out to be all dead.

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: It feels like there is room for improvement here using table statistics and the visibility map to significantly reduce the number of pages retrieved that turn out to be all dead.  Yes, I too wish postgres was more clever for a simple unordered qu

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

Dimitrios Apostolou writes: > 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. Hm. I don't recall exactly how syn

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

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.

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

On Tue, Jan 31, 2023 at 8:28 AM Tom Lane wrote: > Dimitrios Apostolou writes: > > The question is why this simple query is taking so long to complete. > > Do you get the same 10 rows when you repeat the command? > > On the basis of the limited info you provided, I'm going to guess that > > (1) t

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

> On Jan 31, 2023, at 07:40, Dimitrios Apostolou wrote: > Is this bloat even affecting queries that do not use the index? No, but a bloated index often (although not always) goes along with a bloated table. > It seems I have to add VACUUM FULL to nightly maintainance. I wouldn't go that far

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 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_na

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

Dimitrios Apostolou writes: > The question is why this simple query is taking so long to complete. Do you get the same 10 rows when you repeat the command? On the basis of the limited info you provided, I'm going to guess that (1) there are huge subranges of the table containing no live rows, s

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

On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou 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_resu

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

I managed to double-paste different subset of my settings. FWIW all of them are true for all the experiments. Sorry for the confusion. On Tue, 31 Jan 2023, Dimitrios Apostolou wrote: Other postgres settings: shared_buffers = 1024MB effective_io_concurrency = 0 wal_compression = zstd wal_recyc

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

Hello list, I run a very simple SELECT on a huge table (350M rows, 10 columns of type integer or timestamp). The server is an old Mac Mini with 4GB RAM and a slow HDD. All caches are cold either via reboot, or by restarting postgres and using the `purge` command to flush the OS cache. The ques