Re: Casting issues with domains

Le 08/12/2014 16:18, Tom Lane a écrit :
> Thomas Reiss <thomas(dot)reiss(at)dalibo(dot)com> writes:
>> postgres=# explain select * from test2 where a='toto';
>> QUERY PLAN
>> ----------------------------------------------------------
>> Seq Scan on test1 (cost=0.00..1693.00 rows=500 width=5)
>> Filter: (((a)::tstdom)::text = 'toto'::text)
>> (2 lignes)
>
>> As you can see, a is casted to tstdom then again to text. This casts
>> prevents the optimizer to choose an index scan to retrieve the data. The
>> casts are however strictly equivalent and should be not prevent the
>> optimizer to use indexes.
>
> No, they are not equivalent. The optimizer can't simply drop the
> cast-to-domain, because that cast might result in a runtime error due
> to a domain CHECK constraint violation. (This is true even if no such
> constraint exists at planning time, unfortunately. If we had a
> mechanism to force replanning at ALTER DOMAIN ADD CONSTRAINT, maybe the
> no-constraints case could be handled better, but we don't; and adding
> one would also imply adding more locks around domain usage, so it's not
> all that attractive to do it.)
>
> The short answer is that SQL domains are not zero-cost type aliases.
> Perhaps there would be value in having a feature that *is* a a zero-cost
> alias, but it wouldn't be a domain.

I agree regarding the feature for zero-cost aliases. It would ease
access on the catalog done via the information_schema for example.

Thanks for your answer. There's some room for improvement for sure, but
it not as easy as it seems.

Regards,
Thomas