Re: Number of characters in column preventing index usage

On 02/17/2017 11:42 PM, David G. Johnston wrote:
> On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA <hustlerdba(at)gmail(dot)com
> <mailto:hustlerdba(at)gmail(dot)com>>wrote:
>
>
> my_db=# create index tab_idx1 on tab(ID);
>
> CREATE INDEX
> my_db=# explain (analyze, buffers) select count(*) from tab where ID
> = '01625cfa-2bf8-45cf' ;
> QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=8.29..8.30 rows=1 width=0) (actual
> time=0.048..0.048 rows=1 loops=1)
> Buffers: shared read=2
> -> Index Only Scan using tab_idx1 on tab (cost=0.27..8.29
> rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)
> Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
>
>
>
> -> Seq Scan on tab (cost=0.00..14.79 rows=5 width=0) (actual
> time=0.031..0.108 rows=5 loops=1)
> Filter: ((ID)::text =
> '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text)
> Rows Removed by Filter: 218
> Buffers: shared hit=12
> Planning time: 0.122 ms
> Execution time: 0.180 ms
> (8 rows)
>
>
> ​IIRC the only reason the first query cares to use the index is because
> it can perform an Index Only Scan and thus avoid touching the heap at
> all. If it cannot avoid touching the heap the planner is going to just
> use a sequential scan to retrieve the records directly from the heap and
> save the index lookup step.
>

I don't follow - the queries are exactly the same in both cases, except
the parameter value. So both cases are eligible for index only scan.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services