Re: Performance problem with UNION ALL view and domains

On Nov 23, 2007 7:29 AM, Dean Rasheed <dean_rasheed(at)hotmail(dot)com> wrote:
> I am having a performance problem trying to query a view which is a
> UNION ALL of 2 tables. I have narrowed the problem down to my use of
> DOMAINS in the underlying table. So in the test-case below, when the
> column "a" is of domain type foo_text, the query runs slowly using
> the following plan:

I don't know much about DOMAINS, but I did learn somethings about
views, unions and where conditions when I posted a similar performance
question. The best answer was, of course, from Tom Lane here:

http://archives.postgresql.org/pgsql-performance/2007-11/msg00041.php

In my case, the data types in each segment of the union were not
originally identical, preventing the planner from efficiently pushing
the qualifications down to the individual segments prior to the union.

In your case the use of a DOMAIN type may be one of those 'special
cases' forcing the planner to perform the union first, then apply the
conditions.

Jeff