Re: Number of characters in column preventing index usage

On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA <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.

David J.