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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Дата
Msg-id CAKFQuwbL81QYqyCofcw6uw5=0Jictdiw8U2=SRHh5EFFT9W13w@mail.gmail.com
обсуждение исходный текст
Ответ на 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>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Dimitrios Apostolou
Дата:
Сообщение: Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches
Следующее
От: Dimitrios Apostolou
Дата:
Сообщение: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)