Re: Tuning a query with ORDER BY and LIMIT

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. 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)

Cheers,

Jeff