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
Date: Fri, 17 Feb 2017 19:51:43 -0300
Message-ID: <20170217225143.gvcu4d5vqw23ig64@alvherre.pgsql> (raw)
In-Reply-To: <CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@2ndquadrant.com, hustlerdba@gmail.com
Subject: Re: Number of characters in column preventing index usage
In-Reply-To: <20170217225143.gvcu4d5vqw23ig64@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox