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.
