Date: Fri, 17 Feb 2017 19:51:43 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Hustler DBA <hustlerdba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Number of characters in column preventing index usage
Message-ID: <20170217225143.gvcu4d5vqw23ig64@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com>
User-Agent: NeoMutt/20170206-195-7adf12 (1.7.2)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hustler DBA wrote:
> I am seeing this strange behavior, I don't know if this is by design by
> Postgres.
> 
> I have an index on a column which is defined as "character varying(255)".
> When the value I am searching for is of a certain length, the optimizer
> uses the index but when the value is long, the optimizer doesn't use the
> index but does a seq scan on the table. Is this by design? How can I make
> the optimizer use the index no matter what the size/length of the value
> being searched for?

As I recall, selectivity for strings is estimated based on the length of
the string.  Since your sample string looks suspiciously like an UUID,
perhaps you'd be better served by using an UUID column for it, which may
give better results.  This would prevent you from using the shortened
version for searches (which I suppose you can do with LIKE using the
varchar type), but you could replace it with something like this:

select *
from tab
where ID between '01625cfa-2bf8-45cf-0000-000000000000' and
          '01625cfa-2bf8-45cf-ffff-ffffffffffff';

Storage (both the table and indexes) is going to be more efficient this
way too.

-- 
Álvaro Herrera                https://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