From: Tom Lane <tgl@sss.pgh.pa.us>
To: henning.garus@gmail.com
cc: pgsql-general@lists.postgresql.org
Subject: Re: Index usage with differing string types
In-reply-to: <CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi=ofPfVf0_Cfeg@mail.gmail.com>
References: <CAM_PoRsgiecCESyTTfL2iZJB376yaDX1-pfzi=ofPfVf0_Cfeg@mail.gmail.com>
Comments: In-reply-to Henning Garus <henning.garus@gmail.com>
	message dated "Tue, 04 Feb 2025 17:23:39 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <646660.1738686857.1@sss.pgh.pa.us>
Date: Tue, 04 Feb 2025 11:34:17 -0500
Message-ID: <646661.1738686857@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/646661.1738686857%40sss.pgh.pa.us>
Precedence: bulk

Henning Garus <henning.garus@gmail.com> writes:
> However when the String is cast to text the index isn't used:
> explain select * from test where id = 'foo'::text;

That's because "text" is considered a preferred type, so it wins
the contest over whether '=' means texteq or bpchareq:

# explain select * from test where id = 'foo'::varchar;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.15..8.17 rows=1 width=12)
   Index Cond: (id = 'foo'::bpchar)
(2 rows)

# explain select * from test where id = 'foo'::text;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on test  (cost=0.00..40.60 rows=10 width=12)
   Filter: ((id)::text = 'foo'::text)
(2 rows)

Your index supports bpchar comparison semantics, not text,
so it doesn't work for this query.

You could work around this by creating an index on id::text,
but TBH I'd question the choice to use a bpchar column in
the first place.  It's pretty much the poster child for
dubious legacy datatypes.

			regards, tom lane