On 2022-06-22 23:10:25 -0400, Jeff Janes wrote: > On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer <hjp-pg...@hjp.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.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
