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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
