From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: UNION in a VIEW?
Date: Thu, 05 Apr 2001 07:29:42 -0700
Message-ID: <web-34336@davinci.ethosmedia.com> (raw)
In-Reply-To: <9ahrl7$1aih$1@news.tht.net>
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: UNION in a VIEW?
In-Reply-To: <web-34336@davinci.ethosmedia.com>
References: <9ahrl7$1aih$1@news.tht.net> <web-34336@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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