Re: Number of characters in column preventing index usage

Yes, both queries are the same, I just shorten the parameter value to see
what would have happened. The database that I inherited has a column that
stores GUID/UUIDs in a varchar(255) and a select on that table on that
column is doing a FULL TABLE SCAN (seq scan). All the values in the column
are 36 characters long. The table is 104 KB.

I realize that there was no index on that column so when I created the
index and tried to search on a parameter value, it doesn't use the index,
but when I shorten the parameter value then the optimizer decides to use an
index for the search.

On Fri, Feb 17, 2017 at 5:52 PM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>