Re: Re: Unable to understand index only scan as it is not happening for one table while it happens for other

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 <vgmonnet(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>