From: Josh Berkus <josh@agliodbs.com>
To: Shridhar Daithankar <shridhar@frodo.hserus.net>
To: pgsql-performance@postgresql.org
Subject: Re: On the performance of views
Date: Mon, 26 Jan 2004 09:09:41 -0800
Message-ID: <200401260909.41091.josh@agliodbs.com> (raw)
In-Reply-To: <4015326A.8000706@frodo.hserus.net>
References: <40152FF2.8000007@potentialtech.com>
<4015326A.8000706@frodo.hserus.net>
Bill,
> > SELECT a.cola, b.colb, c.colc
> > FROM a JOIN b JOIN c
> > WHERE a.prikey=$1
If your views are simple, PostgreSQL will be able to "push down" any filter
criteria into the view itself. For example,
CREATE view_a AS
SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c;
SELECT * FROM view_a
WHERE a.prikey = 2334432;
will execute just like:
SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c
WHERE a.prikey = 2334432;
However, this does not work for really complex views, which have to be
materialized or executed as a sub-loop.
The "Procedures faster than views" thing is a SQL Server peculiarity which is
a result of MS's buggering up views since they bought the code from Sybase.
> To my understanding, views are expanded at runtime and considered while
> preparing plan for the complete (and possibly bigger) query(Consider a view
> joined with something else). That is not as easy/possible if at all, when it
is
> function. For postgresql query planner, the function is a black box(rightly
so,
> I would say).
Well, as of 7.4 SQL functions are inlined. And simple PL/pgSQL functions
will be "prepared". So it's possible that either could execute as fast as a
view.
Also, if your client is really concerned about no-holds-barred speed, you
should investigate prepared queries.
--
-Josh Berkus
Aglio Database Solutions
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, shridhar@frodo.hserus.net, pgsql-performance@postgresql.org
Subject: Re: On the performance of views
In-Reply-To: <200401260909.41091.josh@agliodbs.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