Re: Problem with ALTER TYPE, Indexes and cast

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Mittwoch, 8. Juli 2015 15:44
> To: Marc Mamin
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast
>
> Marc Mamin <M(dot)Mamin(at)intershop(dot)de> writes:
> > Now I have generated queries that include cast information in order
> to ensure that the indexes get used.
>
> > e.g.: WHERE month1.foo = cast('XY' as character(2))
>
> > with mixed type, this should become something like:
>
> > SELECT ... FROM month1
> > WHERE month1.foo = cast('XY' as character(2)) UNION ALL SELECT...
> > FROM month2 WHERE month2.foo = cast('XY' as varchar)
>
> > which is quite complicated to resolve in our "query builder
> framework"
>
> > There seems to be no way to have dynamic casting, something like:
>
> > WHERE month2.foo = cast('XY' as 'month2.foo'::regtype)
>
> > Is there a way for it ?
>
> If the comparison values are always string literals, then you should
> just drop the casts altogether, ie
>
> WHERE month2.foo = 'XY'
>
> In this sort of situation the literal's type is preferentially resolved
> as being the same as whatever it's being compared to.

I had to dig a bit to find out why I was using the cast.
My issue is that I first clean the literal at some places with text returning functions.
The index won't get used when comparing to text:

create temp table idtest (c character(8));
insert into idtest select cast(s as character(8)) from generate_series(1,39999)s;
create index idtest_c on idtest(c);
analyze idtest;

explain analyze select * from idtest where c = substring (trim('1234567890abc') for 8)

Seq Scan on idtest (cost=0.00..816.99 rows=200 width=9) (actual time=20.302..20.302 rows=0 loops=1)
Filter: ((c)::text = '12345678'::text)

I can easily get rid of the cast while preprocessing the literal before injecting it in the query though.

regards,
Marc Mamin

> regards, tom lane