Re: Tuning a query with ORDER BY and LIMIT

2022-06-25 Thread Peter J. Holzer
On 2022-06-22 23:10:25 -0400, Jeff Janes wrote: > On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer 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),

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Jeff Janes
On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer 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

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Jeff Janes
On Wed, Jun 22, 2022 at 3:39 PM Dirschel, Steve < steve.dirsc...@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

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Tom Lane
"Peter J. Holzer" writes: > On 2022-06-22 19:39:33 +, 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,

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Peter J. Holzer
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

Re: Tuning a query with ORDER BY and LIMIT

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

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Peter J. Holzer
On 2022-06-22 19:39:33 +, 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

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Peter J. Holzer
On 2022-06-22 19:39:33 +, 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

Re: Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Michael van der Kolff
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.dirsc...@thomsonreuters.com> wrote: > I am fairly new to tuning Postgres queries. I have a long background > tuning Oracle

Tuning a query with ORDER BY and LIMIT

2022-06-22 Thread Dirschel, Steve
I am fairly new to tuning Postgres queries. I have a long background tuning Oracle queries. 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,