Thread: UNION in a VIEW?

UNION in a VIEW?

From
"Gordon A. Runkle"
Date:
I have a number of views that I'm bringing over from DB2 which
have UNIONs in them.  Some of the UNIONs have joins.

The views are not working as expected (I'm running 7.1RC2).

It appears that the where clause being applied to the view
by the user is not being distributed properly to the selects.

It's probably easiest to give an example:

There are two tables, itinerary and conjunctive.  A conjunctive
is for an itinerary which has > 4 stops (this is imposed by the
source data, so we have to preserve the relationships).

create table itinerary as (
ticket_nbr,
origin,
dest,
flight_nbr );

create conjunctive as (
ticket_nbr,       -- the original ticket number
conj_ticket_nbr   -- the ticket which extends it
);

I've trimmed them a bit for clarity.

What the view does is this:

create view ticket_conj as (
select ticket_nbr,      origin,      dest,      flight_nbr
from   itinerary
union
select c.ticket_nbr      i.origin,      i.dest,      i.flight_nbr
from   itinerary i      inner join conjunctive c          on i.ticket_nbr = c.conj_ticket_nbr
);

Then we issue queries of this form:

select * from ticket_conj where ticket_nbr = '9483';

Sadly, PostgreSQL goes off and munches for a *long* time,
whereas DB2 and SQL Server return the desired results
promptly.

If I write a query like the view, but giving each select
a where clause, it works (and faster than the other DBs).

It really looks to me (and I am *not* a backend guru) that
the where clause is not being bound to the desired value
(c.ticket_nbr) in the second select.

Does anyone have any ideas on this?

Thanks,

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond


Re: UNION in a VIEW?

From
Tom Lane
Date:
"Gordon A. Runkle" <gar@integrated-dynamics.com> writes:
> I have a number of views that I'm bringing over from DB2 which
> have UNIONs in them.  Some of the UNIONs have joins.

> The views are not working as expected (I'm running 7.1RC2).

> It appears that the where clause being applied to the view
> by the user is not being distributed properly to the selects.

You're correct, an outer WHERE clause will not be pushed down into
the member selects of a UNION.  (This hasn't really got anything
to do with whether a VIEW is involved.)  I haven't gotten round to
convincing myself about whether that transformation is always valid,
or what conditions it needs to be valid.  A TODO item for some
future release...
        regards, tom lane


Re: UNION in a VIEW?

From
"Josh Berkus"
Date:
Tom, Gordon,

> You're correct, an outer WHERE clause will not be pushed down into
> the member selects of a UNION.  (This hasn't really got anything
> to do with whether a VIEW is involved.)  I haven't gotten round to
> convincing myself about whether that transformation is always valid,
> or what conditions it needs to be valid.  A TODO item for some
> future release...

Hmmm ... I'm pretty used an external WHERE clause being applied to the
output of the view, rather than pushed down into the member selects of
the UNION, in the same way as if the UNION query were a subselect.
Coming from a SQL Server background, I'd actually find the suggested
behavior rather confusing (as well as tough for you guys to implement).

-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
 


Re: UNION in a VIEW?

From
"Gordon A. Runkle"
Date:
In article <12713.986422401@sss.pgh.pa.us>, "Tom Lane" <tgl@sss.pgh.pa.us>
wrote:
> You're correct, an outer WHERE clause will not be pushed down into the
> member selects of a UNION.  (This hasn't really got anything to do with
> whether a VIEW is involved.)  I haven't gotten round to convincing
> myself about whether that transformation is always valid, or what
> conditions it needs to be valid.  A TODO item for some future release...

Thanks, Tom.  Not the answer I was hoping for...

This seems to really reduce the utility of UNIONs, especially
within VIEWS.

What DB2 and SQL Server appear to be doing is binding the WHERE
clause to the colums in the positions corresponding to the column
labels in the first select.  So long as that is communicated to
the user, I would think that it would be valid.  At the very least,
it would be consistent with the other major RDBMSs.

I suppose it's to late to put this in for 7.1...  ;-)

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond


Re: UNION in a VIEW?

From
"Gordon A. Runkle"
Date:
In article <web-34027@davinci.ethosmedia.com>, "Josh Berkus"
<josh@agliodbs.com> wrote:

> Tom, Gordon,
> Hmmm ... I'm pretty used an external WHERE clause being applied to the
> output of the view, rather than pushed down into the member selects of
> the UNION, in the same way as if the UNION query were a subselect.
> Coming from a SQL Server background, I'd actually find the suggested
> behavior rather confusing (as well as tough for you guys to implement).

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).

So, I can only guess what's happening "behind the curtain", but this
is what I'm observing.

Plus, I think that if the WHERE clause were applied to the results
of the VIEW, that would require storing those results in temp space,
and for large tables would be very slow.  I believe that's the whole
reason that VIEWs' underlying queries can be merged/rewritten with
the "calling" query?

Thanks,

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond


Re: Re: UNION in a VIEW?

From
"Josh Berkus"
Date:
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
 


Re: Re: UNION in a VIEW?

From
"Gordon A. Runkle"
Date:
In article <web-34336@davinci.ethosmedia.com>, "Josh Berkus"
<josh@agliodbs.com> wrote:
> 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.

This sounds right to me.  (Which is easy for me to say, as
I'm not the one with the know-how to actually do it!)  I'd 
like to see cases like my example (simple joins) work, since
that's the main reason for doing the UNION in the first place.
I came up with a data workaround in my case, so I'm set for now)

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond


Re: UNION in a VIEW?

From
"Gordon A. Runkle"
Date:
In article <web-34027@davinci.ethosmedia.com>, "Josh Berkus"
<josh@agliodbs.com> wrote:

> Tom, Gordon,

> Hmmm ... I'm pretty used an external WHERE clause being applied to the
> output of the view, rather than pushed down into the member selects of
> the UNION, in the same way as if the UNION query were a subselect.
> Coming from a SQL Server background, I'd actually find the suggested
> behavior rather confusing (as well as tough for you guys to implement).

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).

So, I can only guess what's happening "behind the curtain", but this
is what I'm observing.

Plus, I think that if the WHERE clause were applied to the results
of the VIEW, that would require storing those results in temp space,
and for large tables would be very slow.  I believe that's the whole
reason that VIEWs' underlying queries can be merged/rewritten with
the "calling" query?

Thanks,

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond