Re: Tuning a query with ORDER BY and LIMIT

On 2022-06-22 23:48:37 +0200, Peter J. Holzer wrote:
> 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;
> [...]
> The index cannot be used for sorting, since the column used for sorting
> isn't in the first position in the index.

compared to a single value

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

Another - better - optimization would be to fetch the first 50 results
for each of the 6 possible values of result, then choose the 50 largest
of those. That sounds tricky to generalize, though.

hp

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