|
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 4th 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 |
