pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunion optimization in views
3+ messages / 3 participants
[nested] [flat]
* union optimization in views
@ 2002-08-29 04:47 JOE <joe@piscitella.com>
0 siblings, 1 reply; 3+ messages in thread
From: JOE @ 2002-08-29 04:47 UTC (permalink / raw)
To: pgsql-sql
We are attempting to move a couple of systems from Oracle to Postgres but can not do so without application rewrites due to the current use of views with UNIONs and the criticality of the performances of these views.
I was wondering if a decision has been made on the optimization with the UNION clause in views. There are many documents in the SQL archive showing that the "push down" is not occuring and thus the use of UNION's in views is limited to case where the data set is small or performance is not a consideration. I also looked through the TODO list and didn't see anything (of course I could have missed references).
thanks - Joe
snip of an Article from SQL archives
CREATE VIEW two_tables AS
SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id
FROM t1
UNION ALL
SELECT t2.id, t2.name, NULL, t2.juris_id
FROM t2;
This works fine as a view, since I have made the id's unique between
the two tables (using a sequence). However, as t1 has 100,000 records, it is
vitally important that queries against this view use an index.
As it is a Union view, though, they ignore any indexes:
> It's probably not pushing the login='asdadad' condition down into the
> queries in the view so it's possibly doing a full union all followed
> by the condition (given that it's estimating a larger number of rows
> returned). I think there was some question about whether it was safe
> to do that optimization (ie, is select * from (a union [all] b) where
> condition always the same as
> select * from a where condition union [all]
> select * from b where condition
> )
>
> There wasn't any final determination --- it's still an open issue
> whether there are any limitations the planner would have to consider
> when trying to push down conditions into UNIONs. Offhand it seems to
> me that the change is always safe when dealing with UNION ALL, but I'm
> not quite convinced about UNION. And what of INTERSECT and EXCEPT?
>
> Another interesting question is whether there are cases where the
> planner could legally push down the condition, but should not because
> it would end up with a slower plan. I can't think of any examples
> offhand, but that doesn't mean there aren't any.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union optimization in views
@ 2002-08-29 17:52 Josh Berkus <josh@agliodbs.com>
parent: JOE <joe@piscitella.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2002-08-29 17:52 UTC (permalink / raw)
To: JOE <joe@piscitella.com>; pgsql-sql
Joe,
> We are attempting to move a couple of systems from Oracle to Postgres but
can not do so without application rewrites due to the current use of views
with UNIONs and the criticality of the performances of these views.
>
> I was wondering if a decision has been made on the optimization with the
UNION clause in views. There are many documents in the SQL archive showing
that the "push down" is not occuring and thus the use of UNION's in views is
limited to case where the data set is small or performance is not a
consideration. I also looked through the TODO list and didn't see anything
(of course I could have missed references).
I'd take this up on PGSQL-HACKERS. The UNION VIEW optimization, last I
checked, was stalled mainly because nobody wanted to work on it. Maybe you
can?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union optimization in views
@ 2002-09-03 04:06 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-09-03 04:06 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: JOE <joe@piscitella.com>; pgsql-sql
On Thu, 29 Aug 2002, Josh Berkus wrote:
> Joe,
>
> > We are attempting to move a couple of systems from Oracle to Postgres but
> can not do so without application rewrites due to the current use of views
> with UNIONs and the criticality of the performances of these views.
> >
> > I was wondering if a decision has been made on the optimization with the
> UNION clause in views. There are many documents in the SQL archive showing
> that the "push down" is not occuring and thus the use of UNION's in views is
> limited to case where the data set is small or performance is not a
> consideration. I also looked through the TODO list and didn't see anything
> (of course I could have missed references).
>
> I'd take this up on PGSQL-HACKERS. The UNION VIEW optimization, last I
> checked, was stalled mainly because nobody wanted to work on it. Maybe you
> can?
Tom sent a patch to -patches against then cvs head that did
union/intersect [all] push down last week. Unless someone comes
up with a complaint, I'd guess that's going to make it in 7.3
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-03 04:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-29 04:47 union optimization in views JOE <joe@piscitella.com>
2002-08-29 17:52 ` Josh Berkus <josh@agliodbs.com>
2002-09-03 04:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox