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

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: Dimitrios Apostolou <jimis(at)gmx(dot)net>
Cc: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Date: 2023-01-31 15:42:48
Message-ID: DC283F75-96B6-4BD6-844F-D0B1A47666E6@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> On Jan 31, 2023, at 07:40, Dimitrios Apostolou <jimis(at)gmx(dot)net> 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; that's basically changing your oil every time you get gas. However, monitoring bloat and either rebuilding the indexes (if they're all that's bloated) or using pg_repack [1] periodically is a good practice.

[1] https://github.com/reorg/pg_repack

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2023-01-31 15:43:07 Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Previous Message Dimitrios Apostolou 2023-01-31 15:40:06 Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches