Содержание
Вопрос или проблема
У меня есть таблица билетов (Postgres на Heroku), в которой около 7 миллионов записей.
У меня есть один запрос к таблице, который часто выполняется с полем “exported”, равным true или false. Когда оно равно true, он выполняется очень быстро (~30 мс). Когда же оно равно false, выполнение занимает много времени (~7000 мс).
Я перестроил индексы и выполнил VACCUM. Похоже, что система просто не может использовать индексы. В частности, этот:
CREATE INDEX index_tickets_owner_exported_deleted_archived_created ON public.tickets USING btree (owner_id, deleted_at, exported, archived, created_at)
Вот запрос в обоих случаях с EXPLAIN….
МЕДЛЕННЫЙ: exported = false занимает 7 секунд
explain (buffers, analyze, verbose) SELECT “tickets”.* FROM “tickets” WHERE “tickets”.”deleted_at” IS NULL AND “tickets”.”owner_id” = 1211 AND “tickets”.”exported” = false AND “tickets”.”archived” = false ORDER BY tickets.created_at desc LIMIT 200 OFFSET 0;
Limit (cost=0.09..1352.68 rows=200 width=1054) (actual time=20.736..7755.424 rows=161 loops=1)
Output: id, order_id, number, gross_amount, tare_amount, status, completed_at, canceled_at, created_at, updated_at, net_amount, driver_id, plan_item_id, verified_at, verifier_id, truck_number, creator_id, is_flagged, company_id, signer_id, started_at, haul_type, deleted_at, terms_url, ruckit_trip_id, origin_name, paper_ticket_image_url, created_at_latitude, completed_at_latitude, created_at_longitude, completed_at_longitude, material_name, material_unit, loading_location_name, unloading_location_name, signed_at, signed_at_latitude, signed_at_longitude, cost_code, hours, signed_at_timezone, concrete_mix_code, concrete_type, concrete_inspection_rejected, concrete_pump_wash_out_bags, concrete_pump_primer, verifier_type, verifier_note, exported, exported_at, exported_guid, group_tag, multi_page_tag, multi_page_pdf_url, material_invoice_item_id, trucking_invoice_item_id, original_created_at, source, rotated, held_for_questioning, approver_id, approved_at, approval_note, approved, trucking_invoice_status, material_invoice_status, qa_air, qa_slump, qa_temp, qc_air, qc_slump, qc_temp, o_air, o_slump, o_temp, ocr_complete, note, cycle_time_data, total_cost, connex_exported_at, connex_export_id, black_flagged, driver_name, owner_id, custom_field_1, custom_field_2, custom_field_3, custom_field_4, site_list, verification_locked_at, archived, inventory_status, ocr_behavior, ocr_behavior_set_date, last_ruckit_update_at, hauler_lookup_status, hauler_id, plan_item_lookup_status, inventory_transaction_id, paper_ticket_date, project_id
Buffers: shared hit=4674873
-> Index Scan Backward using index_tickets_on_created_at on public.tickets (cost=0.09..910705.14 rows=134661 width=1054) (actual time=20.734..7755.402 rows=161 loops=1)
Output: id, order_id, number, gross_amount, tare_amount, status, completed_at, canceled_at, created_at, updated_at, net_amount, driver_id, plan_item_id, verified_at, verifier_id, truck_number, creator_id, is_flagged, company_id, signer_id, started_at, haul_type, deleted_at, terms_url, ruckit_trip_id, origin_name, paper_ticket_image_url, created_at_latitude, completed_at_latitude, created_at_longitude, completed_at_longitude, material_name, material_unit, loading_location_name, unloading_location_name, signed_at, signed_at_latitude, signed_at_longitude, cost_code, hours, signed_at_timezone, concrete_mix_code, concrete_type, concrete_inspection_rejected, concrete_pump_wash_out_bags, concrete_pump_primer, verifier_type, verifier_note, exported, exported_at, exported_guid, group_tag, multi_page_tag, multi_page_pdf_url, material_invoice_item_id, trucking_invoice_item_id, original_created_at, source, rotated, held_for_questioning, approver_id, approved_at, approval_note, approved, trucking_invoice_status, material_invoice_status, qa_air, qa_slump, qa_temp, qc_air, qc_slump, qc_temp, o_air, o_slump, o_temp, ocr_complete, note, cycle_time_data, total_cost, connex_exported_at, connex_export_id, black_flagged, driver_name, owner_id, custom_field_1, custom_field_2, custom_field_3, custom_field_4, site_list, verification_locked_at, archived, inventory_status, ocr_behavior, ocr_behavior_set_date, last_ruckit_update_at, hauler_lookup_status, hauler_id, plan_item_lookup_status, inventory_transaction_id, paper_ticket_date, project_id
Filter: ((tickets.deleted_at IS NULL) AND (NOT tickets.exported) AND (NOT tickets.archived) AND (tickets.owner_id = 1211))
Rows Removed by Filter: 6780764
Buffers: shared hit=4674873
Query Identifier: -4426566847541555156
Planning Time: 0.456 ms
Execution Time: 7755.497 ms
БЫСТРЫЙ exported = true
explain (buffers, analyze, verbose) SELECT “tickets”.* FROM “tickets” WHERE “tickets”.”deleted_at” IS NULL AND “tickets”.”owner_id” = 1211 AND “tickets”.”exported” = true AND “tickets”.”archived” = false ORDER BY tickets.created_at desc LIMIT 200 OFFSET 0;
Limit (cost=0.09..3790.43 rows=200 width=1054) (actual time=30.772..32.322 rows=200 loops=1)
Output: id, order_id, number, gross_amount, tare_amount, status, completed_at, canceled_at, created_at, updated_at, net_amount, driver_id, plan_item_id, verified_at, verifier_id, truck_number, creator_id, is_flagged, company_id, signer_id, started_at, haul_type, deleted_at, terms_url, ruckit_trip_id, origin_name, paper_ticket_image_url, created_at_latitude, completed_at_latitude, created_at_longitude, completed_at_longitude, material_name, material_unit, loading_location_name, unloading_location_name, signed_at, signed_at_latitude, signed_at_longitude, cost_code, hours, signed_at_timezone, concrete_mix_code, concrete_type, concrete_inspection_rejected, concrete_pump_wash_out_bags, concrete_pump_primer, verifier_type, verifier_note, exported, exported_at, exported_guid, group_tag, multi_page_tag, multi_page_pdf_url, material_invoice_item_id, trucking_invoice_item_id, original_created_at, source, rotated, held_for_questioning, approver_id, approved_at, approval_note, approved, trucking_invoice_status, material_invoice_status, qa_air, qa_slump, qa_temp, qc_air, qc_slump, qc_temp, o_air, o_slump, o_temp, ocr_complete, note, cycle_time_data, total_cost, connex_exported_at, connex_export_id, black_flagged, driver_name, owner_id, custom_field_1, custom_field_2, custom_field_3, custom_field_4, site_list, verification_locked_at, archived, inventory_status, ocr_behavior, ocr_behavior_set_date, last_ruckit_update_at, hauler_lookup_status, hauler_id, plan_item_lookup_status, inventory_transaction_id, paper_ticket_date, project_id
Buffers: shared hit=22462
-> Index Scan Backward using index_tickets_on_created_at on public.tickets (cost=0.09..910705.14 rows=48054 width=1054) (actual time=30.771..32.297 rows=200 loops=1)
Output: id, order_id, number, gross_amount, tare_amount, status, completed_at, canceled_at, created_at, updated_at, net_amount, driver_id, plan_item_id, verified_at, verifier_id, truck_number, creator_id, is_flagged, company_id, signer_id, started_at, haul_type, deleted_at, terms_url, ruckit_trip_id, origin_name, paper_ticket_image_url, created_at_latitude, completed_at_latitude, created_at_longitude, completed_at_longitude, material_name, material_unit, loading_location_name, unloading_location_name, signed_at, signed_at_latitude, signed_at_longitude, cost_code, hours, signed_at_timezone, concrete_mix_code, concrete_type, concrete_inspection_rejected, concrete_pump_wash_out_bags, concrete_pump_primer, verifier_type, verifier_note, exported, exported_at, exported_guid, group_tag, multi_page_tag, multi_page_pdf_url, material_invoice_item_id, trucking_invoice_item_id, original_created_at, source, rotated, held_for_questioning, approver_id, approved_at, approval_note, approved, trucking_invoice_status, material_invoice_status, qa_air, qa_slump, qa_temp, qc_air, qc_slump, qc_temp, o_air, o_slump, o_temp, ocr_complete, note, cycle_time_data, total_cost, connex_exported_at, connex_export_id, black_flagged, driver_name, owner_id, custom_field_1, custom_field_2, custom_field_3, custom_field_4, site_list, verification_locked_at, archived, inventory_status, ocr_behavior, ocr_behavior_set_date, last_ruckit_update_at, hauler_lookup_status, hauler_id, plan_item_lookup_status, inventory_transaction_id, paper_ticket_date, project_id
Filter: ((tickets.deleted_at IS NULL) AND tickets.exported AND (NOT tickets.archived) AND (tickets.owner_id = 1211))
Rows Removed by Filter: 28056
Buffers: shared hit=22462
Query Identifier: -4426566847541555156
Planning Time: 0.432 ms
Execution Time: 32.383 ms
ВОТ ИНДЕКСЫ В ЭТОЙ ТАБЛИЦЕ
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-------------------------------------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------
public | tickets | tickets_pkey | | CREATE UNIQUE INDEX tickets_pkey ON public.tickets USING btree (id)
public | tickets | index_tickets_on_approver_id | | CREATE INDEX index_tickets_on_approver_id ON public.tickets USING btree (approver_id)
public | tickets | index_tickets_on_archived | | CREATE INDEX index_tickets_on_archived ON public.tickets USING btree (archived)
public | tickets | index_tickets_on_company_id | | CREATE INDEX index_tickets_on_company_id ON public.tickets USING btree (company_id)
public | tickets | index_tickets_on_connex_export_id | | CREATE INDEX index_tickets_on_connex_export_id ON public.tickets USING btree (connex_export_id)
public | tickets | index_tickets_on_cost_code | | CREATE INDEX index_tickets_on_cost_code ON public.tickets USING btree (cost_code)
public | tickets | index_tickets_on_created_at | | CREATE INDEX index_tickets_on_created_at ON public.tickets USING btree (created_at)
public | tickets | index_tickets_on_custom_field_1 | | CREATE INDEX index_tickets_on_custom_field_1 ON public.tickets USING btree (custom_field_1)
public | tickets | index_tickets_on_custom_field_2 | | CREATE INDEX index_tickets_on_custom_field_2 ON public.tickets USING btree (custom_field_2)
public | tickets | index_tickets_on_deleted_at | | CREATE INDEX index_tickets_on_deleted_at ON public.tickets USING btree (deleted_at)
public | tickets | index_tickets_on_driver_id | | CREATE INDEX index_tickets_on_driver_id ON public.tickets USING btree (driver_id)
public | tickets | index_tickets_on_exported | | CREATE INDEX index_tickets_on_exported ON public.tickets USING btree (exported)
public | tickets | index_tickets_on_group_tag | | CREATE INDEX index_tickets_on_group_tag ON public.tickets USING btree (group_tag)
public | tickets | index_tickets_on_hauler_id | | CREATE INDEX index_tickets_on_hauler_id ON public.tickets USING btree (hauler_id)
public | tickets | index_tickets_on_hauler_lookup_status | | CREATE INDEX index_tickets_on_hauler_lookup_status ON public.tickets USING btree (hauler_lookup_status)
public | tickets | index_tickets_on_held_for_questioning | | CREATE INDEX index_tickets_on_held_for_questioning ON public.tickets USING btree (held_for_questioning)
public | tickets | index_tickets_on_inventory_status | | CREATE INDEX index_tickets_on_inventory_status ON public.tickets USING btree (inventory_status)
public | tickets | index_tickets_on_inventory_transaction_id | | CREATE INDEX index_tickets_on_inventory_transaction_id ON public.tickets USING btree (inventory_transaction_id)
public | tickets | index_tickets_on_is_flagged | | CREATE INDEX index_tickets_on_is_flagged ON public.tickets USING btree (is_flagged)
public | tickets | index_tickets_on_material_invoice_item_id | | CREATE INDEX index_tickets_on_material_invoice_item_id ON public.tickets USING btree (material_invoice_item_id)
public | tickets | index_tickets_on_multi_page_tag | | CREATE INDEX index_tickets_on_multi_page_tag ON public.tickets USING btree (multi_page_tag)
public | tickets | index_tickets_on_number | | CREATE INDEX index_tickets_on_number ON public.tickets USING btree (number)
public | tickets | index_tickets_on_order_id | | CREATE INDEX index_tickets_on_order_id ON public.tickets USING btree (order_id)
public | tickets | index_tickets_on_original_created_at | | CREATE INDEX index_tickets_on_original_created_at ON public.tickets USING btree (original_created_at)
public | tickets | index_tickets_on_owner_id | | CREATE INDEX index_tickets_on_owner_id ON public.tickets USING btree (owner_id)
public | tickets | index_tickets_on_owner_id_and_deleted_at_and_archived | | CREATE INDEX index_tickets_on_owner_id_and_deleted_at_and_archived ON public.tickets USING btree (owner_id, deleted_at, archived)
public | tickets | index_tickets_on_plan_item_id | | CREATE INDEX index_tickets_on_plan_item_id ON public.tickets USING btree (plan_item_id)
public | tickets | index_tickets_on_plan_item_lookup_status | | CREATE INDEX index_tickets_on_plan_item_lookup_status ON public.tickets USING btree (plan_item_lookup_status)
public | tickets | index_tickets_on_rotated | | CREATE INDEX index_tickets_on_rotated ON public.tickets USING btree (rotated)
public | tickets | index_tickets_on_ruckit_trip_id | | CREATE INDEX index_tickets_on_ruckit_trip_id ON public.tickets USING btree (ruckit_trip_id)
public | tickets | index_tickets_on_signer_id | | CREATE INDEX index_tickets_on_signer_id ON public.tickets USING btree (signer_id)
public | tickets | index_tickets_on_trucking_invoice_item_id | | CREATE INDEX index_tickets_on_trucking_invoice_item_id ON public.tickets USING btree (trucking_invoice_item_id)
public | tickets | index_tickets_on_verification_locked_at | | CREATE INDEX index_tickets_on_verification_locked_at ON public.tickets USING btree (verification_locked_at)
public | tickets | index_tickets_on_verified_at | | CREATE INDEX index_tickets_on_verified_at ON public.tickets USING btree (verified_at)
public | tickets | index_tickets_on_verifier_id_and_verifier_type | | CREATE INDEX index_tickets_on_verifier_id_and_verifier_type ON public.tickets USING btree (verifier_id, verifier_type)
public | tickets | index_tickets_on_approved | | CREATE INDEX index_tickets_on_approved ON public.tickets USING btree (approved)
public | tickets | index_tickets_on_project_id | | CREATE INDEX index_tickets_on_project_id ON public.tickets USING btree (project_id)
public | tickets | index_tickets_owner_exported_deleted_archived_created | | CREATE INDEX index_tickets_owner_exported_deleted_archived_created ON public.tickets USING btree (owner_id, deleted_at, exported, archived, created_at)
Я буду признателен за любую помощь, которую вы можете оказать.
Ответ или решение
Почему запросы к PostgreSQL выполняются быстро при exported = true и медленно при exported = false
Ваша ситуация с запросами к таблице tickets в PostgreSQL, особенно в контексте разного времени выполнения для значений true и false в поле exported, является распространенной проблемой, связанной с оптимизацией запросов и использованием индексов. В данной статье мы подробно разберем причины этого поведения и предложим возможные решения для улучшения производительности запросов.
1. Статистика и распределение данных
Первое, что следует рассмотреть, это распределение данных в таблице. Если у вас 7 миллионов записей, стоит проверить, насколько распределены значения в поле exported. Обычно значения true и false могут быть неравномерно распределены, что крайне влияет на план выполнения запроса.
- Статистика: Запустите команду
ANALYZE tickets;, чтобы обновить статистику, которую PostgreSQL использует для планирования запросов. Это особенно важно, если данные относительно недавно добавлены или изменены.
2. План выполнения запроса
Ваши запросы имеют различные планы выполнения, что видно из результатов EXPLAIN. В случае exported = false, PostgreSQL использует последовательное сканирование и значительно большее количество строк удаляется фильтром:
-
Запрос с
exported = false:- Было обработано 6,780,764 строк, что является значительной нагрузкой для вашей базы данных, и, следовательно, результаты запроса выводятся медленно.
-
Запрос с
exported = true:- Обработка 28,056 строк приводит к более быстрому ответу, поскольку система обрабатывает меньше данных.
3. Перепроектирование индекса
Ваша текущая структура индекса index_tickets_owner_exported_deleted_archived_created включает поле exported, но порядок и сочетание столбцов могут не подходить для частоты использования значений true и false. Оптимизируйте индекс, учитывая наиболее часто используемые условия.
- Перепроектируйте/index: Рассмотрите возможность создания отдельных индексов для
exported, какindex_tickets_on_exportedили более сложные составные индексы, которые учитывают частоту использования других фильтров.
4. Фильтрация данных и буферы
Согласно результатам EXPLAIN, наблюдается значительная разница в количестве используемых буферов. Для запроса при exported = false были обработаны 4,674,873 буфера, что подтверждает, что система обращается к большому количеству ненужных данных.
- Проверка конфигурации: Оптимизируйте параметры конфигурации вашей базы данных, такие как
work_memиshared_buffers, чтобы уменьшить количество обращений к диску и повысить общую производительность.
5. Параллелизм
Проверьте, используете ли параллельные запросы. Для выполнения больших выборок в PostgreSQL может быть полезным использовать параллелизм. Убедитесь, что ваши серверные настройки позволяют параллельное выполнение запросов.
- Настройки параллелизма: Проверьте параметры, такие как
max_parallel_workers_per_gather, для того чтобы убедиться, что база данных может использовать несколько потоков для выполнения запросов.
Заключение
Для повышения производительности запросов к таблице tickets при работе с полем exported, необходимо провести всесторонний анализ распределения данных, оптимизацию индексов и настройку конфигурации базы данных. Начните с обновления статистики, проанализируйте существующие индексы и их назначение, а также убедитесь, что вы используете возможности параллелизма PostgreSQL. Ваша база данных сможет более эффективно обрабатывать запросы, что позволит сократить время выполнения в случаях, когда значение exported равно false.