Я храню версии документа в PostgreSQL 9.4. Каждый раз, когда пользователь создает новую версию, он вставляет строку, чтобы я мог отслеживать все изменения с течением времени. Каждая строка разделяет столбец reference_id
с предыдущими строками. Некоторые строки утверждаются, а некоторые остаются черновиками. Каждая строка также имеет viewable_at
время.
id | reference_id | approved | viewable_at | created_on | content
1 | 1 | true | 2015-07-15 00:00:00 | 2015-07-13 | Hello
2 | 1 | true | 2015-07-15 11:00:00 | 2015-07-14 | Guten Tag
3 | 1 | false | 2015-07-15 17:00:00 | 2015-07-15 | Grüß Gott
Наиболее частый запрос — получить строки, сгруппированные по reference_id, где approved
равно true
, а viewable_at
меньше текущего времени. (В этом случае в результаты будет включена строка с идентификатором 2)
На данный момент это лучший запрос, который я придумал, который не требует добавления дополнительных столбцов:
SELECT DISTINCT ON (reference_id) reference_id, id, approved, viewable_at, content
FROM documents
WHERE approved = true AND viewable_at <= '2015-07-15 13:00:00'
ORDER BY reference_id, created_at DESC`
У меня есть индекс для reference_id и многоколоночный индекс для утвержденных и viewable_at.
Всего 15 000 строк, на моем локальном компьютере это все еще составляет в среднем несколько сотен миллисекунд (140–200). Я подозреваю, что отчетливый звонок или заказ могут замедлять его.
Как наиболее эффективно хранить эту информацию, чтобы запросы SELECT были наиболее производительными?
Результат EXPLAIN (БУФЕРЫ, АНАЛИЗ):
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6668.86..6730.36 rows=144 width=541) (actual time=89.862..99.613 rows=145 loops=1)
Buffers: shared hit=2651, temp read=938 written=938
-> Sort (cost=6668.86..6699.61 rows=12300 width=541) (actual time=89.861..97.796 rows=13184 loops=1)
Sort Key: reference_id, created_at
Sort Method: external merge Disk: 7488kB
Buffers: shared hit=2651, temp read=938 written=938
-> Seq Scan on documents (cost=0.00..2847.80 rows=12300 width=541) (actual time=0.049..40.579 rows=13184 loops=1)
Filter: (approved AND (viewable_at < '2015-07-20 06:46:55.222798'::timestamp without time zone))
Rows Removed by Filter: 2560
Buffers: shared hit=2651
Planning time: 0.218 ms
Execution time: 178.583 ms
(12 rows)
Примечания по использованию документа:
Документы редактируются вручную, и мы еще не сохраняем документы автоматически каждые X секунд или что-то в этом роде, поэтому объем будет достаточно низким. На данный момент существует в среднем 7 версий и в среднем только 2 утвержденных версии на каждый reference_id. (~30%)
Что касается минимальных и максимальных значений, подавляющее большинство документов будет иметь 1 или 2 версии, и маловероятно, что какой-либо документ будет иметь более 30 или 40. Существует процесс сборки мусора для очистки неутвержденных версий старше недели, поэтому общее количество версий должно оставаться довольно низким.
Для извлечения и практического использования я мог бы использовать лимит/смещение для запросов, но в моих тестах это не имеет большого значения. В идеале это базовый запрос, который заполняет представление или что-то еще, чтобы я мог выполнять дополнительные запросы поверх этих результатов, но я не совсем уверен, как это повлияет на результирующую производительность, и открыт для предложений. У меня сложилось впечатление, что если я смогу сделать это хранилище/запрос как можно более простым/быстрым, то все остальные запросы, которые начинаются с этой точки, можно было бы улучшить, но вполне вероятно, что я ошибаюсь и каждый запрос требует более независимого обдумывания.
EXPLAIN (BUFFERS, ANALYZE)
было бы предпочтительнее. ОбычныйANALYZE
не содержит много полезной информации. - person Craig Ringer   schedule 20.07.2015