Re: Tuning a query with ORDER BY and LIMIT

On 2022-06-22 19:39:33 +0000, Dirschel, Steve wrote:
> 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;
[...]
> 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.

The index cannot be used for sorting, since the column used for sorting
isn't in the first position in the index. That's just how btree indexes
work and Oracle will have the same limitation. What would be possible is
to use an index only scan (returning 2,634,718 matching results), sort
that to find the 50 newest entries and retrieve only those from the
table. That should be faster since the index contains only 4 of 28 (if I
counted correctly) columns and should be quite a bit smaller. It's
possible that Oracle does this. But I'm not sure whether you could tell
that from the execution plan.

        hp

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