Re: Tuning a query with ORDER BY and LIMIT

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)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