PG_HAZEL : ожидания СУБД PostgreSQL при избытке индексов⁠⁠

Взято с основного технического канала Postgres DBA

Начало экспериментов :

PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Задача эксперимента

Определение и анализ характерных ожиданий, вызванных использованием индексов при массовых операциях INSERT.

Сравнительные эксперименты

Эксперимент-1 : Стандартный сценарий "Insert only"

Эксперимент-2 : Cценарий "Insert only" с использование индексов на таблице.

Сценарий "Insert only"

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)

VALUES ( current_tid , current_bid , current_aid , current_delta , CURRENT_TIMESTAMP );

Тестовая таблица

Table "public.pgbench_history"

Column | Type | Collation | Nullable | Default

--------+-----------------------------+-----------+----------+---------

tid | integer | | |

bid | integer | | |

aid | integer | | |

delta | integer | | |

mtime | timestamp without time zone | | |

filler | character(22) | | |

Foreign-key constraints:

"pgbench_history_aid_fkey" FOREIGN KEY (aid) EFERENCES pgbench_accounts(aid)

"pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

"pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

Тестовая таблица с добавленными индексами (индексы по столбцам aid , delta, mtime)

Table "public.pgbench_history"

Column | Type | Collation | Nullable | Default

--------+-----------------------------+-----------+----------+---------

tid | integer | | |

bid | integer | | |

aid | integer | | |

delta | integer | | |

mtime | timestamp without time zone | | |

filler | character(22) | | |

Indexes:

"pgbench_history_idx1" btree (aid)

"pgbench_history_idx2" btree (delta)

"pgbench_history_idx3" btree (mtime)

Foreign-key constraints:

"pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)

"pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

"pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

Операционная скорость и медианное время тестового SQL запроса

Результат

Создание дополнительных индексов ухудшило скорость на 16-18% и увеличило время на 24-28%.

Корреляция между типами ожиданий и ожиданиями СУБД

Результат

1. Использование индексов резко увеличивает ожидания типа IO и LWLock.

Корреляция между типом ожидания и событиями ожидания при выполнении тестового запроса

Тип ожидания "IO"

Результат

• Резкий рост корреляции с ожиданием DataFileRead

Тип ожидания "Lock"

Тип ожидания "LWLock"

Результат

Резкий рост корреляции с событием ожидания CheckpointerComm.

Итог и результаты анализа

Отключение индексов при массовых операциях вставки данных дает прирост операционной скорости 16-18% .

Характерными признаками наличия лишних индексов при преобладании операция вставки по таблице являются:

1. Высокое значение коэффициента корреляции с событием ожидания IO/DataFileRead , LWLock/BufferMapping и LWLock/CheckpointerComm

BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.

CheckpointerComm : Ожидание при управлении запросами fsync.