On Wed, 31 Jan 2024 at 09:09, Philip Semanchuk <phi...@americanefficient.com> wrote: > So in your case those 5m rows that you deleted were probably still clogging > up your table until you ran VACUUM FULL.
It seems more likely to me that the VACUUM removed the rows and just left empty pages in the table. Since there's no index on expires_at, the only way to answer that query is to Seq Scan and Seq Scan will need to process those empty pages. While that processing is very fast if the page's item pointers array is empty, it could still be slow if the page needs to be read from disk. Laurenz's request for the explain (analyze, buffers) output with track_io_timing on will help confirm this. If it is just reading empty pages that's causing this issue then adding that missing index would improve the situation after running just plain VACUUM each time there's a bulk delete. David
