Re: Tuning a query with ORDER BY and LIMIT

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(at)hjp(dot)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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"