Re: [PERFORM] Number of characters in column preventing index usage - Mailing list pgsql-performance

From Alvaro Herrera
Subject Re: [PERFORM] Number of characters in column preventing index usage
Date
Msg-id 20170217225143.gvcu4d5vqw23ig64@alvherre.pgsql
Whole thread Raw
In response to [PERFORM] Number of characters in column preventing index usage  (Hustler DBA <hustlerdba@gmail.com>)
List pgsql-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


pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: [PERFORM] Number of characters in column preventing index usage
Next
From: Tomas Vondra
Date:
Subject: Re: [PERFORM] Number of characters in column preventing index usage