Tuning a query with ORDER BY and LIMIT

What do you see when you remove the LIMIT clause? It may be possible to
rewrite this using ROW_NUMBER.

--Michael

On Thu, Jun 23, 2022 at 5:39 AM Dirschel, Steve <
steve.dirschel@thomsonreuters.com> 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;

[...]

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 Wed, Jun 22, 2022 at 3:39 PM Dirschel, Steve <
steve.dirschel@thomsonreuters.com> wrote:

explain (analyze, verbose, costs, buffers, timing, summary, hashes)

'hashes', what's that? Are you using community PostgreSQL, or someones
fork?

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.

Can you show the plan from Oracle? I'm not an expert at reading their
plans, but certainly they are easier to attempt to read if you can at least
see them.

I was questioning if the result IN clause was causing the issue so I ran
the query with result = and see the same results:

I can't reproduce this at all. With simple equality it absolutely uses the
index for ordering in that case, even in v10.

Cheers,

Jeff

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. I suspect this may be what Oracle is doing. With
some trickery, you can get PostgreSQL to do the same thing.

(select * from foo where a=4 order by b)
union all
(select * from foo where a=7 order by b)
order by b
limit 50

QUERY PLAN

-----------------------------------------------------------------------------------------------------
Limit (cost=0.86..131.25 rows=50 width=8)
-> Merge Append (cost=0.86..26079.86 rows=10000 width=8)
Sort Key: foo.b
-> Index Only Scan using foo_a_b_idx on foo (cost=0.42..12939.92
rows=5000 width=8)
Index Cond: (a = 4)
-> Index Only Scan using foo_a_b_idx on foo foo_1
(cost=0.42..12939.92 rows=5000 width=8)
Index Cond: (a = 7)

Cheers,

Jeff

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