Thread: [GENERAL] Unable to understand index only scan as it is not happening for onetable while it happens for other

Please help me to understand the following,

*For the following query Index Only Scan to be performance as I am querying
the indexed fields alone. There are 20 fields in this table, inclusive of
the three I am selecting.*
localdb=# explain analyse verbose select uid, guid from mm where uid=100
limit 1;
                                                                   QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.27..8.29 rows=1 width=45) (actual time=0.017..0.017 rows=1
loops=1)
   Output: uid, guid
   ->  Index Scan using mm_pkey on public.mm  (cost=0.27..8.29 rows=1
width=45) (actual time=0.016..0.016 rows=1 loops=1)
         Output: uid, guid
         Index Cond: (mm.uid = 100)
 Planning time: 0.149 ms
 Execution time: 0.042 ms
(7 rows)

*Index only scan runs for the following*
localdb=# explain analyze verbose select id, z from test order by id limit
20;
                                                            QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..10.29 rows=20 width=17) (actual time=0.017..0.027
rows=20 loops=1)
   Output: id, z
   ->  Index Only Scan using zx on public.test  (cost=0.28..1502.47
rows=3000 width=17) (actual time=0.016..0.024 rows=20 loops=1)
         Output: id, z
         Heap Fetches: 20
 Planning time: 0.080 ms
 Execution time: 0.050 ms
(7 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-tp5968835.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Ah! Got it. Thanks. One more question,

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)



-----
--
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-tp5968835p5968844.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

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)
         Output: uid
         Filter: (mm.uid > 100)
         Rows Removed by Filter: 46
 Planning time: 0.109 ms
 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.

thanks for the explanation, Gary.



-----
--
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-tp5968835p5968976.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.