Обсуждение: 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!"

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!"

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2022-06-23 00:19:19 +0200, Peter J. Holzer wrote:
> On 2022-06-22 23:48:37 +0200, Peter J. Holzer wrote:
> > 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
  ^ not

E-Mail really needs a "Supercedes" header.

        hp

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

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> 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.

Yeah, that sure looks like it should work.  Since we've not been
shown the whole table's DDL, it's hard to guess at why it isn't;
maybe there's a data type issue?

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

That's worked for many ages.

            regards, tom lane

On 2022-06-22 23:10:25 -0400, Jeff Janes wrote:
> On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>     >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.
>
>
> You don't even need to read 50 from each of the 6 branches.  If you use a merge
> append operation, you would need to read  55 rows.  50 to be returned, and one
> non-returned from each branch other than the one returning the last row.

Yes, but that means a lot of jumping around in the index.

        hp

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