Есть ли способ ускорить это сканирование кучи Postgres?
База данных новичка здесь. Это мой запрос, я использую Postgres 9.3.5:
=# explain analyse SELECT SUM(actual_cost) as cost, SUM(total_items)
as num_items, processing_date FROM frontend_items
WHERE chemical_id='0501013B0' GROUP BY processing_date;
И это план запроса:
HashAggregate (cost=1648624.91..1648624.92 rows=1 width=16) (actual time=12591.844..12591.848 rows=17 loops=1)
-> Bitmap Heap Scan on frontend_items (cost=14520.24..1643821.35 rows=640474 width=16) (actual time=254.841..12317.746 rows=724242 loops=1)
Recheck Cond: ((chemical_id)::text = '0501013B0'::text)
-> Bitmap Index Scan on frontend_items_chemical_id_varchar_pattern_ops_idx (cost=0.00..14360.12 rows=640474 width=0) (actual time=209.538..209.538 rows=724242 loops=1)
Index Cond: ((chemical_id)::text = '0501013B0'::text)
Total runtime: 12592.499 ms
Как видите, это Bitmap Heap Scan это занимает большую часть времени. Есть ли способ ускорить это?
При необходимости я могу создать больше индексов: мои данные почти доступны только для чтения (они обновляются раз в месяц).
Я предполагаю, что я ничего не могу поделать, учитывая, что мне нужно несколько атрибутов, за исключением платы за достаточное количество ОЗУ для хранения всей базы данных в памяти, но предложения будут очень благодарны.
Возможно, я мог бы просто посмотреть один из этих атрибутов за раз, если это ускорит процесс.
NB: я запускаю это на Macbook с 16 ГБ ОЗУ и SSD. Я поставил shared_buffers до 4 ГБ и work_mem до 40 МБ. В конечном итоге я буду использовать сервер с 32 ГБ оперативной памяти и SSD.
ОБНОВЛЕНИЕ: схема таблицы выглядит следующим образом:
Column | Type | Modifiers
-------------------+-------------------------+--------------------------------------------------------------------
id | integer | not null default nextval('frontend_items_id_seq'::regclass)
presentation_code | character varying(15) | not null
presentation_name | character varying(1000) | not null
total_items | integer | not null
net_cost | double precision | not null
actual_cost | double precision | not null
quantity | double precision | not null
processing_date | date | not null
price_per_unit | double precision | not null
chemical_id | character varying(9) | not null
pct_id | character varying(3) | not null
practice_id | character varying(6) | not null
sha_id | character varying(3) | not null
Indexes:
"frontend_items_pkey" PRIMARY KEY, btree (id)
"frontend_items_45fff4c7" btree (sha_id)
"frontend_items_4e2e609b" btree (pct_id)
"frontend_items_528f368c" btree (processing_date)
"frontend_items_6ea07fe3" btree (practice_id)
"frontend_items_a69d813a" btree (chemical_id)
"frontend_items_b9b2c7ab" btree (presentation_code)
"frontend_items_chemical_id_varchar_pattern_ops_idx" btree (chemical_id varchar_pattern_ops)
"frontend_items_pct_code_id_488a8bbfb2bddc6d_like" btree (pct_id varchar_pattern_ops)
"frontend_items_practice_id_bbbafffdb2c2bf1_like" btree (practice_id varchar_pattern_ops)
"frontend_items_presentation_code_69403ee04fda6522_like" btree (presentation_code varchar_pattern_ops)
"frontend_items_presentation_code_varchar_pattern_ops_idx" btree (presentation_code varchar_pattern_ops)
Foreign-key constraints:
"front_chemical_id_4619f68f65c49a8_fk_frontend_chemical_bnf_code" FOREIGN KEY (chemical_id) REFERENCES frontend_chemical(bnf_code) DEFERRABLE INITIALLY DEFERRED
"frontend__practice_id_bbbafffdb2c2bf1_fk_frontend_practice_code" FOREIGN KEY (practice_id) REFERENCES frontend_practice(code) DEFERRABLE INITIALLY DEFERRED
"frontend_items_pct_id_30c06df242c3d1ba_fk_frontend_pct_code" FOREIGN KEY (pct_id) REFERENCES frontend_pct(code) DEFERRABLE INITIALLY DEFERRED
"frontend_items_sha_id_4fa0ca3c3b9b67f_fk_frontend_sha_code" FOREIGN KEY (sha_id) REFERENCES frontend_sha(code) DEFERRABLE INITIALLY DEFERRED
И вот вывод подробного объяснения:
# explain (verbose, buffers, analyse) SELECT SUM(actual_cost) as cost, SUM(total_items) as num_items, processing_date FROM frontend_items WHERE chemical_id='0501012G0' GROUP BY processing_date;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1415349.73..1415349.74 rows=1 width=16) (actual time=3048.551..3048.556 rows=17 loops=1)
Output: sum(actual_cost), sum(total_items), processing_date
Buffers: shared hit=141958 read=12725
-> Bitmap Heap Scan on public.frontend_items (cost=11797.55..1411446.84 rows=520385 width=16) (actual time=213.889..2834.911 rows=524644 loops=1)
Output: id, presentation_code, presentation_name, total_items, net_cost, actual_cost, quantity, processing_date, price_per_unit, chemical_id, pct_id, practice_id, sha_id
Recheck Cond: ((frontend_items.chemical_id)::text = '0501012G0'::text)
Buffers: shared hit=141958 read=12725
-> Bitmap Index Scan on frontend_items_chemical_id_varchar_pattern_ops_idx (cost=0.00..11667.46 rows=520385 width=0) (actual time=172.574..172.574 rows=524644 loops=1)
Index Cond: ((frontend_items.chemical_id)::text = '0501012G0'::text)
Buffers: shared hit=2 read=2012
Total runtime: 3049.177 ms
3 ответа
У вас 724242 строки, и запрос занимает 12592,499 мс. Это 0,017387 мс на строку, то есть 57514 строк в секунду. На что ты жалуешься? Я думаю, что ваш запрос достаточно быстрый. Обычные жесткие диски поддерживают только 65–200 строк в секунду при использовании сканирования индекса, хотя сканирование индекса / кучи растрового изображения выполняется быстрее. Я думаю, вы обнаружите, что PostgreSQL использует наилучший возможный план запросов для вашей ситуации.
Если вы выполните запрос еще раз, он станет быстрее? Кеши будут горячими, поэтому повторное выполнение может быть быстрее. Если это не станет быстрее, маловероятно, что поможет больше памяти. Размер страницы данных PostgreSQL составляет 8 КБ, поэтому вы получаете не более 724242*8 КБ = 5,5 ГБ данных, т.е. данные должны помещаться в вашу оперативную память.
Редактировать: второй запрос, упомянутый в отредактированной версии вопроса, показывает производительность 172000 строк в секунду. Таким образом, возможно, что такие запросы становятся быстрее, если данные кэшируются в оперативной памяти. Я бы выбрал подход встраивания всего набора данных в оперативную память. Оперативная память дешева, но время разработчика дорого.
Поскольку выбранные строки специфичны, в этом случае может оказаться полезным добавление индекса с несколькими столбцами. Это может помочь подтолкнуть планировщика к использованию сканирования индекса вместо сканирования растрового изображения.
CREATE INDEX ON frontend_items(chemical_id, processing_date, total_items, actual_cost);
Обратите внимание, что это возможно, но стоит попробовать при оптимизации запроса.
Если вам нужны значения для нескольких элементов, процесс можно сделать намного быстрее (это помогло мне сократить время подсчета денормализованных значений с 12 часов до 12 минут).
Вы можете запросить значение для всех элементов (
Запрос будет выглядеть так:
SELECT chemical_id, SUM(actual_cost) as cost, SUM(total_items)
as num_items, processing_date FROM frontend_items
GROUP BY chemical_id, processing_date;
Когда я тестировал его на таблице с ~8 миллионами записей, один запрос выполнялся примерно за 0,5 секунды, а запрос агрегированных значений для элементов - за 2 секунды.
Вы также можете сохранить эти результаты в денормализованных полях и показать их пользователям (если их не волнует немного устаревшее значение).