From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
Cc: | "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Tuning a query with ORDER BY and LIMIT |
Date: | 2022-06-22 23:22:13 |
Message-ID: | 3695492.1655940133@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-06-22 23:26:29 | Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8) |
Previous Message | Peter J. Holzer | 2022-06-22 22:25:58 | Re: Tuning a query with ORDER BY and LIMIT |