Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, Jun 27, 2017 at 1:09 PM, rajan <vgmonnet@xxxxxxxxx> wrote:
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)

First guess would be that all the information needed from the index alone.  In other words for an index only scan to work all fields retrieved have to be accessible from the same index. 





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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux