Problem with ALTER TYPE, Indexes and cast

Marc Mamin <M.Mamin@intershop.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.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mittwoch, 8. Juli 2015 15:44
To: Marc Mamin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast

Marc Mamin <M.Mamin@intershop.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general