Re: Re: UNION in a VIEW?

Gordon,

> Like I said, I'm not a backend guru.  However, SQL Server and DB2
> both *appear* to be pushing down the WHERE clause.  They may not be,
> but they both process the query nearly instantaneously on large
> tables, which leads me to speculate that they do.  PostgreSQL goes
> off and munches for a *long* time on the same view/query, whereas if
> I write a query which explicitly distributes the WHERE then
> PostgreSQL
> processes the query very fast (faster than DB2 or SQL Server).

Good to know.  Actually, when you put it like that, you must be right
... saved UNION Views in which the underlying tables have indexes use
them (or, at least, show a performance boost when the indexes are
updated) on a filter; that would imply a push-down of the filter
conditions.  

However, what makes things tricky for Tom is that for some Views ...
with transformations, CASE and/or subselects, SQL Server does filter the
output insead, because "pushing down" the where clause is impossible.
Thus such views tend to have run times of 30 sec - 1 minute.  I know
because I'm currently re-writing a bunch of these.

Tom ... and I realize that we're talking about features for version 7.3
or later ... a direct way to approach it would be to do a push-down on
simple UNION views, and to do output filtering on UNION views wich
contain a CASE, any subselect, or CAST expression (or similar) on the
filtered columns.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco