Question 413852 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Query fast when field TRUE and slow when FALSE

I have a tickets table (Postgres on Heroku) that has about 7 million records.

I have a single table query that is run often with the exported field either true or false. When it is true, it runs very fast (~30ms). When it is false, it is very slow (~7000ms).

I have rebuilt the indexes and have run a VACCUM. It seems like it is just not able to utilize the indexes. Specifically, this one:

CREATE INDEX index_tickets_owner_exported_deleted_archived_created
   ON public.tickets USING btree
   (owner_id, deleted_at, exported, archived, created_at);

Here is the query both ways with the EXPLAIN:

SLOW: exported = false takes 7 sec

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

FAST 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

HERE ARE THE INDEXES ON THAT TABLE

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)

I appreciate any help you guys can give.

Tags:

2

Answers


  1. 0

    There are two approaches you can take:

    1. Improve the estimates.

      As a first step, ANALYZE the table and see if that makes a difference. If yes, you need to tune autovacuum so that it analyzes the table more often:

      ALTER TABLE tickets SET (autovacuum_analyze_scale_factor = 0.01);

      If that doesn’t have an effect, you can try collecting more detailed statistics. You can use ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ... to make PostgreSQL calculate more "most common values" and a more detailed histogram. Then ANALYZE the table again and see if you can make a difference.

      Finally, there are extended statistics to cover functional dependencies:

      CREATE STATISTICS mystats
ON owner_id, exported, archived, deleted_at
FROM tickets;

ANALYZE tickets;

      If none of that helps:

    2. You can rewrite the ORDER BY condition so that PostgreSQL cannot use the index on created_at:

      ORDER BY created_at + INTERVAL '0' DESC

      Then the optimizer has to resort to other indexes.

    As others mentioned, you probably have too many indexes on that table.

    Login or Signup to reply.
  2. 0

    Due to a limitation in the planner’s interior logic, PostgreSQL will not use an index to derive ORDER after the inclusion of an IS NULL condition on a previous column, which is apparently what you were hoping to achieve with the specific index you mentioned. There is no logical reason that this can’t happen, the planner has just not been augmented to allow for it.

    You could circumvent this limitation by taking the column tested for IS NULL out of the index body and putting in a WHERE condition:

    created index on tickets 
  (owner_id, exported, archived, created_at) 
  where deleted_at is null;

    This will only make it possible to use that index, but it might still not look attractive enough to actually choose it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search