Re: Tuning a query with ORDER BY and LIMIT

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer <hjp-pgsql@xxxxxx> 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