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

On Tue, Jun 27, 2017 at 1:09 PM, rajan <vgmonnet(at)gmail(dot)com> 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(at)postgresql(dot)org)
> 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.
http://www.efficito.com/learn_more