[PERFORM] Slow vacuum of GIST indexes, because of random reads on PostgreSQL 9.6

Поиск
Список
Период
Сортировка
От Marcin Barczynski
Тема [PERFORM] Slow vacuum of GIST indexes, because of random reads on PostgreSQL 9.6
Дата
Msg-id CAOhG4wem20oFCyZhGW-WyqJcno45z2trPnvsDrDUcPbOytFycg@mail.gmail.com
обсуждение исходный текст
Список pgsql-performance
I am using a GIST index on timestamp range, because it supports 'contains' operator ('@>'). Unfortunately, in large scale (billions of rows, index size: almost 800 GB) vacuuming the index takes an order of magnitude longer than btrees (days/weeks instead of hours). 
According to the code, during vacuum gist index is traversed in a logical order which translates into random disk acceses (function gistbulkdelete in gistvacuum.c). Btree indexes are vacuummed in physical order (function btvacuumscan in nbtree.c).

As a workaround, I'm planning to replace all uses of 'contains' with the following function:

    CREATE OR REPLACE FUNCTION tstzrange_contains(
        range tstzrange,
        ts timestamptz)
    RETURNS bool AS
    $$
    SELECT (ts >= lower(range) AND (lower_inc(range) OR ts > lower(range)))
       AND (ts <= upper(range) AND (upper_inc(range) OR ts < upper(range)))
    $$ LANGUAGE SQL IMMUTABLE;

and create btree indexes on lower and upper bound:

    CREATE INDEX my_table_time_range_lower_idx ON my_table (lower(time_range));
    CREATE INDEX my_table_time_range_upper_idx ON my_table (upper(time_range));

Is it the best approach?

--
Best regards,
Marcin Barczynski

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

Предыдущее
От: Soni M
Дата:
Сообщение: [PERFORM] OS cache management
Следующее
От: Gerardo Herzig
Дата:
Сообщение: Re: [PERFORM] OS cache management