Subject: Re: Number of characters in column preventing index usage
To: pgsql-performance@postgresql.org
References: 
 <CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com>
 <CAKFQuwZrKf52hNtc124zEVgNgQ7cG18yY2ynJJPLhTHpzarihg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <195020ec-053f-6387-25f8-3f354da6cc38@2ndquadrant.com>
Date: Fri, 17 Feb 2017 23:52:43 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAKFQuwZrKf52hNtc124zEVgNgQ7cG18yY2ynJJPLhTHpzarihg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance