Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Date
Msg-id CAKFQuwbL81QYqyCofcw6uw5=0Jictdiw8U2=SRHh5EFFT9W13w@mail.gmail.com
Whole thread Raw
In response to Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches  (Dimitrios Apostolou <jimis@gmx.net>)
List pgsql-general
Tree view
On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
On Tue, 31 Jan 2023, David G. Johnston wrote:
>
> It feels like there is room for improvement here using table statistics and the visibility map to significantly reduce the number of pages retrieved that turn out to be all dead. 

Yes, I too wish postgres was more clever for a simple unordered query. I
should also mention that this table has two btree indices, one primary key
index and one on another column. Would it be feasible to "descend" an
index in order to immediately find some live data?

That isn't possible since indexes don't have tuple visibility information within them; most of the dead bloat entries present in the table have dead bloat entries pointing to them in the index.

David J.

pgsql-general by date:

Previous
From: Dimitrios Apostolou
Date:
Subject: Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Next
From: Dimitrios Apostolou
Date:
Subject: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)