pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)
4+ messages / 4 participants
[nested] [flat]
* VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)
@ 2023-02-01 18:27 Dimitrios Apostolou <jimis@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Dimitrios Apostolou @ 2023-02-01 18:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 space in my case?
I'm now running VACUUM FULL as everyone suggested. I just tried plain
VACUUM as I was curious if it would work and because it doesn't lock
the table with an operation that takes hours for my setup.
Thanks,
Dimitris
^ permalink raw reply [nested|flat] 4+ messages in thread
* 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)
@ 2023-02-01 18:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Dimitrios Apostolou <jimis@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-02-01 18:45 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou <jimis@gmx.net> 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 idea why it
> failed to reclaim the space in my case?
>
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. You already had lots of
completely dead pages at the start of the table and after vacuum those
pages still exist (pages are sequentially numbered and gapless) just with a
different kind of dead contents.
In short, say you have 20 pages, 1-10 dead and 11-20 alive. The only way
to change which pages exist and get rid of 1-10 is to rewrite the table
putting the contents in 11-20 into the newly recreated pages 1-10. VACUUM
doesn't do that - it just makes it so when new data is written they can be
placed into the abandoned 1-10 range. If for some reason page 21 existed
and VACUUM saw it was all dead it would remove page 21 from the end of the
relation since that wouldn't affect "sequential and gapless".
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* 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)
@ 2023-02-01 18:47 Christophe Pettus <xof@thebuild.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Christophe Pettus @ 2023-02-01 18:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Dimitrios Apostolou <jimis@gmx.net>; pgsql-general@lists.postgresql.org
> On Feb 1, 2023, at 10:45, David G. Johnston <david.g.johnston@gmail.com> 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: Couldn't it skip pages that are marked appropriately in the visibility map?
^ permalink raw reply [nested|flat] 4+ messages in thread
* 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)
@ 2023-02-01 18:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-02-01 18:56 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Dimitrios Apostolou <jimis@gmx.net>; pgsql-general@lists.postgresql.org
Christophe Pettus <xof@thebuild.com> writes:
>> On Feb 1, 2023, at 10:45, David G. Johnston <david.g.johnston@gmail.com> 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: Couldn't it skip pages that are marked appropriately in the visibility map?
There's no bit in the visibility map for "contains no live tuples".
I think adding one would be optimizing for the wrong thing.
(You might be thinking of the free-space map, but we don't maintain
that rigorously enough to trust it as a guide to skipping pages.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-02-01 18:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-01 18:27 VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches) Dimitrios Apostolou <jimis@gmx.net>
2023-02-01 18:45 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-01 18:47 ` Christophe Pettus <xof@thebuild.com>
2023-02-01 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox