Thread: UNION in a VIEW?
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
"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
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
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
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
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
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
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