Hi Rajan, I would say that the optimiser has calculated that it would be quicker to sequentially read thought the table to get the 354 rows returned without the limit. By introducing the limit, it is much faster to pick out the first 10 rows using the index. Using an index is usually only quicker when a small percentage of the table like 5% is going to be returned, when more than 5-8% of the rows are to be returned the optimiser will generally go for a sequential scan.
Cheers Gary On Wed, Jun 28, 2017 at 11:22 AM, rajan <vgmon...@gmail.com> wrote: > Thanks. > > Now I did the same query, but it is going for *index-only scan* only after > I > put *limit* > > localdb=# explain analyse verbose select uid from mm where uid>100 order by > uid; > QUERY PLAN > ------------------------------------------------------------ > ------------------------------------------------------------- > Sort (cost=30.99..31.87 rows=354 width=8) (actual time=0.243..0.294 > rows=354 loops=1) > Output: uid > Sort Key: mm.uid > Sort Method: quicksort Memory: 41kB > -> Seq Scan on public.mm (cost=0.00..16.00 rows=354 width=8) (actual > time=0.010..0.123 rows=354 loops=1) > Execution time: 0.342 ms > (10 rows) > > localdb=# explain analyse verbose select uid from mm where uid>100 order by > uid *limit 10*; > > QUERY > PLAN > ------------------------------------------------------------ > ------------------------------------------------------------ > -------------------------------- > Limit (cost=0.27..2.13 rows=10 width=8) (actual time=0.026..0.037 rows=10 > loops=1) > Output: uid > -> Index Only Scan using mm_pkey on public.mm (cost=0.27..65.91 > rows=354 width=8) (actual time=0.025..0.034 rows=10 loops=1) > Output: uid > Index Cond: (mm.uid > 100) > Heap Fetches: 10 > Planning time: 0.096 ms > Execution time: 0.059 ms > (8 rows) > > > > ----- > -- > Thanks, > Rajan. > -- > View this message in context: http://www.postgresql-archive. > org/Unable-to-understand-index-only-scan-as-it-is-not- > happening-for-one-table-while-it-happens-for-other-tp5968835p5968971.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
