Re: [GENERAL] Why so long?

Поиск
Список
Период
Сортировка
От Steve Clark
Тема Re: [GENERAL] Why so long?
Дата
Msg-id 1016f7cc-3acf-b6ab-d1d4-cd29df85fe7b@netwolves.com
обсуждение исходный текст
Ответ на Re: [GENERAL] Why so long?  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general
Дерево обсуждения
On 04/19/2017 11:57 AM, Jeff Janes wrote:
On Wed, Apr 19, 2017 at 8:24 AM, Steve Clark <steve.clark@netwolves.com> wrote:
Hello,

I am confused. I have a table that has an incrementing primary key id.

When I select max(id) from table is returns almost instantly but
when I select min(id) from table it takes longer than I want to wait.

Shouldn't postgresql be able to quickly find the minimum id value in the index?

Not if the low end of the index is stuffed full of obsolete entries, which haven't been cleaned up because it is not being vacuumed often enough.

Do you have autovacuum on?  Have you manually vacuumed the table recently?

Cheers,

Jeff
Hi Jeff,

Autovacuum is turned on.

 schemaname |        relname        | last_vacuum |        last_autovacuum        | vacuum_count | autovacuum_count
------------+-----------------------+-------------+-------------------------------+--------------+------------------
 public     | netflow               |             | 2017-04-11 01:18:53.261221-04 |            0 |                1


It is a large table.
select pg_size_pretty(pg_relation_size('netflow'));
 pg_size_pretty
----------------
 1267 GB

select pg_size_pretty(pg_total_relation_size('netflow_pkey'));
 pg_size_pretty
----------------
 287 GB

Regards,
Steve

В списке pgsql-general по дате отправления:

Предыдущее
От: Moreno Andreo
Дата:
Сообщение: Re: [GENERAL] Recover corrupted data
Следующее
От: Alexandre
Дата:
Сообщение: Re: [GENERAL] Recover corrupted data