Re: [PERFORM] 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@gmail.com
> <mailto:hustlerdba@gmail.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