Re: Tuning a query with ORDER BY and LIMIT

I am fairly new to tuning Postgres queries.  I have a long background tuning Oracle queries.

 

Posrgres version 10.11

 

Here is the DDL for the index the query is using:

 

create index workflow_execution_initial_ui_tabs

    on workflow_execution (workflow_id asc, status asc, result asc, completed_datetime desc);

 

 

explain (analyze, verbose, costs, buffers, timing, summary, hashes)

select * from workflow_execution

where workflow_id = 14560 and

      status = 'COMPLETED' and

      result in ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')

order by completed_datetime desc limit 50;

 

--

Limit  (cost=56394.91..56395.04 rows=50 width=1676) (actual time=3400.608..3400.622 rows=50 loops=1)

"  Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

  Buffers: shared hit=142368

  ->  Sort  (cost=56394.91..56432.71 rows=15118 width=1676) (actual time=3400.607..3400.615 rows=50 loops=1)

"        Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

        Sort Key: workflow_execution.completed_datetime DESC

        Sort Method: top-N heapsort  Memory: 125kB

        Buffers: shared hit=142368

        ->  Index Scan using workflow_execution_initial_ui_tabs on workflow.workflow_execution  (cost=0.69..55892.70 rows=15118 width=1676) (actual time=0.038..2258.579 rows=2634718 loops=1)

"              Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

"              Index Cond: ((workflow_execution.workflow_id = 14560) AND ((workflow_execution.status)::text = 'COMPLETED'::text) AND ((workflow_execution.result)::text = ANY ('{SUCCEEDED,REEXECUTED,ABORTED,DISCONTINUED,FAILED,PARTIAL_SUCCESS}'::text[])))"

              Buffers: shared hit=142368

Planning time: 0.217 ms

Execution time: 3400.656 ms

 

With Oracle for a query like this since the index is on the 3 columns matching the WHERE clause and the ORDER BY clause is in the 4^th position Oracle would be able to scan that index and as soon as it finds the first matching 50 rows.  But as you can see above Postgres is finding 2,634,718 matching rows for the WHERE clause , sorts them, and then returns the first 50 rows.   

 

I was questioning if the result IN clause was causing the issue so I ran the query with result = and see the same results:

 

explain (analyze, verbose, costs, buffers, timing, summary, hashes)

select * from workflow_execution

where workflow_id = 14560 and

      status = 'COMPLETED' and

      result = 'SUCCEEDED'

order by completed_datetime desc limit 50;

 

Limit  (cost=54268.09..54268.22 rows=50 width=1676) (actual time=3372.453..3372.467 rows=50 loops=1)

"  Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

  Buffers: shared hit=140313

  ->  Sort  (cost=54268.09..54304.46 rows=14547 width=1676) (actual time=3372.452..3372.460 rows=50 loops=1)

"        Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

        Sort Key: workflow_execution.completed_datetime DESC

        Sort Method: top-N heapsort  Memory: 125kB

        Buffers: shared hit=140313

        ->  Index Scan using workflow_execution_initial_ui_tabs on workflow.workflow_execution  (cost=0.69..53784.85 rows=14547 width=1676) (actual time=0.034..2238.867 rows=2616747 loops=1)

"              Output: execution_id, state_machine_id, workflow_id, started_datetime, completed_datetime, status, execution_context_s3_arn, ol_version, created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, correlation_id, result, state_machine_execution_arn, created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, subscription_guid"

              Index Cond: ((workflow_execution.workflow_id = 14560) AND ((workflow_execution.status)::text = 'COMPLETED'::text) AND ((workflow_execution.result)::text = 'SUCCEEDED'::text))

              Buffers: shared hit=140313

Planning time: 0.264 ms

Execution time: 3372.511 ms

 

 

Is Postgres unable to optimize the query similar to Oracle?  Is it possible this is possible but we are running on too old of a version?

 

Thanks in advance for any input.

 

Steve

 

 

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html