Re: Casting issues with domains

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.

regards, tom lane