Re: Tuning a query with ORDER BY and LIMIT

On 2022-06-22 19:39:33 +0000, Dirschel, Steve wrote:
> 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 = 'SUCCEEDED'
> order by completed_datetime desc limit 50;

This query should actually be able to use the index, since the first
columns in the index are all compared to single values. So the you can
just jump to the first matching index and then get the next 50 entries.

> 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?

PostgreSQL 10 is quite old, so that's a possibility.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"