From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Tuning a query with ORDER BY and LIMIT |
Date: | 2022-06-22 22:19:19 |
Message-ID: | 20220622221919.pnm7hgu5r7rxfwae@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Zheng Li | 2022-06-22 22:22:00 | Re: Support logical replication of DDLs |
Previous Message | Peter J. Holzer | 2022-06-22 22:13:21 | Re: Tuning a query with ORDER BY and LIMIT |