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

[Gary Evans]

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  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
>

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

[Albe Laurenz]

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