On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer <hjp-pgsql@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. I suspect this may be what Oracle is doing. With some trickery, you can get PostgreSQL to do the same thing.
(select * from foo where a=4 order by b)
union all
(select * from foo where a=7 order by b)
order by b
limit 50
QUERY PLAN ----------------------------------------------------------------------------------------------------- Limit (cost=0.86..131.25 rows=50 width=8) -> Merge Append (cost=0.86..26079.86 rows=10000 width=8) Sort Key: foo.b -> Index Only Scan using foo_a_b_idx on foo (cost=0.42..12939.92 rows=5000 width=8) Index Cond: (a = 4) -> Index Only Scan using foo_a_b_idx on foo foo_1 (cost=0.42..12939.92 rows=5000 width=8) Index Cond: (a = 7)