On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou <jimis@xxxxxxx> 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.
