Re: [GENERAL] Re: Unable to understand index only scan as it is nothappening for one table while it happens for other

rajan wrote:
> why the index-only scan *works only* with an *order by*?
> localdb=# explain analyse verbose select uid from mm where uid>100 *order
> by* uid;
>                                                                     QUERY
> PLAN
> --------------------------------------------------------------------------------
>  Index Only Scan using mm_pkey on public.mm  (cost=0.27..22.47 rows=354 width=8)
>                                      (actual time=0.023..0.079 rows=354 loops=1)
>    Output: uid
>    Index Cond: (mm.uid > 100)
>    Heap Fetches: 0
>  Planning time: 0.096 ms
>  Execution time: 0.131 ms
> (6 rows)

I'd guess that it would work fine, but PostgreSQL chooses to use a sequential
scan instead, because too many rows meet the condition "uid > 100".

If you add the ORDER BY, the plan with the sequential scan also has to
sort the data, which makes it much more expensive, while the index only scan
returns the data in sorted order anyway and does not have to sort,
which makes it cheaper.

Yours,
Laurenz Albe