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) - Mailing list pgsql-general

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".