Обсуждение: Performance of views

Hi

Generally, I have avoided using VIEWS within application code and only
used them for client interfaces, the sole reason being the performance
of views against tables.

Changes to database over the past few years appear to have improved the
performance of views, but I am still not comfortable with using VIEWS
within application code. The main reasons I have are

   * Data within a view is not necessary sequential, unlike a table
   * Higher overhead mapping to original tables and indexes
   * Danger of linking views and tables and not utilising utilising
     underlying tables properly.

Am I right to avoid to VIEWS within application code?

Simon

--
Simon

Simon Windsor
Eml: simon.windsor@cornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599

On Sun, Nov 2, 2008 at 4:11 PM, Simon Windsor
<simon.windsor@cornfield.me.uk> wrote:
> Hi
>
> Generally, I have avoided using VIEWS within application code and only used
> them for client interfaces, the sole reason being the performance of views
> against tables.

Have you confirmed this belief with tests?  Or just following some
kind of general knowledge that's filtered down from other dbas?

> Changes to database over the past few years appear to have improved the
> performance of views, but I am still not comfortable with using VIEWS within
> application code. The main reasons I have are

This is true for some other dbms'.  It's generally not true for
PostgreSQL.  PostgreSQL views have for a very long history of running
as queries the same as if you ran them by hand.  This is due to the
use of an advanced rules system that allows for the same performance
of views as for the original queries on the table.

>  * Data within a view is not necessary sequential, unlike a table

Data in a pgsql view is EXACTLY the same as if you ran the query by hand.

>  * Higher overhead mapping to original tables and indexes

The overhead in pgsql is measured in sub  millisecond time.

>  * Danger of linking views and tables and not utilising utilising
>    underlying tables properly.

not sure what you mean there at all.

>
> Am I right to avoid to VIEWS within application code?

Yes.  Absolutely.  If you're running MySQL.  OTOH, if you're in pgsql
there's no real reason to avoid them.  unless you'll also be
supporting mysql.  then mysql plays the lowest common denominator
trump card it so often does and forces you to dumb down your db layer
to make it happy.

>> Am I right to avoid to VIEWS within application code?

How one uses views is more a matter of taste and best practices, than
a matter of rules like this.  Frankly, this "rule" sounds rather ill
conceived.

My feeling is that views can be difficult to maintain when they are
nested, but otherwise use them whenever they simplify matters.  I also
feel that they should only be defined for when they represent, well,
views of the data that make sense in a long term way; don't use them
if for a one-off application.

As for performance -- let me reiterate:  create the most elegant
design, possibly with views, and only worry about performance AFTER
PROFILING.

-W

Simon,

* Simon Windsor (simon.windsor@cornfield.me.uk) wrote:
> Generally, I have avoided using VIEWS within application code and only
> used them for client interfaces, the sole reason being the performance
> of views against tables.

Views really shouldn't have a large impact on overall performance.  In
PostgreSQL, that's even more true.  In general, I would strongly
recommend moving complex queries from your application into views in the
database.  The performance difference really should be minimal, while
the maintainability is improved.

>   * Data within a view is not necessary sequential, unlike a table

I've got no idea what you're talking about here, to be honest.  Pulling
data out of a table has no guarenteed ordering to it unless you
explicitly ask for one, and you can do that in a view too.

>   * Higher overhead mapping to original tables and indexes

This just plain isn't true in PG, at least, and I'd think most other
sensible databases..

>   * Danger of linking views and tables and not utilising utilising
>     underlying tables properly.

If you push your complicated queries into your views and keep the
application code simpler, identifying and fixing performance or other
issues often becomes easier, and easier to fix..

> Am I right to avoid to VIEWS within application code?

No.

    Thanks,

        Stephen

On Sun, Nov 2, 2008 at 6:39 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Simon,
>
>>   * Higher overhead mapping to original tables and indexes
>
> This just plain isn't true in PG, at least, and I'd think most other
> sensible databases..

Note that, at least in older versions, MySQL completely materialized a
temporary table from a view, then used that for the view.  This is
horribly inefficient, and results in a lot of people thinking views
are slow.  Not sure if this has been addressed in MySQL yet, don't
really care anymore, since I rarely use mysql for anything anymore.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Note that, at least in older versions, MySQL completely materialized a
> temporary table from a view, then used that for the view.  This is
> horribly inefficient, and results in a lot of people thinking views
> are slow.  Not sure if this has been addressed in MySQL yet, don't
> really care anymore, since I rarely use mysql for anything anymore.

Some simple experiments with mysql 5.0.67 suggest that this meme is
obsolete there too.  I found some cases where it looks like we optimize
a bit better than they do, but for simple views you seem to get the
same plan as if you'd written out the equivalent query in-line.

            regards, tom lane

We've been toying around with reworking our years old database schema and replacing the old tables with updatable views into the new schema.  The only real problem we've had with it is that queries to one of our views seem to be joining on unnecessary tables because the view does the join.  We don't need the columns provided by the join and the join is kind of costly, but performance has been great otherwise.

does anyone  know if postgres support 'refresh' of applicable index(es) of a materialized view on refresh?

Thanks,
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> To: scott.marlowe@gmail.com
> CC: simon.windsor@cornfield.me.uk; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Performance of views
> Date: Sun, 2 Nov 2008 21:22:24 -0500
> From: tgl@sss.pgh.pa.us
>
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > Note that, at least in older versions, MySQL completely materialized a
> > temporary table from a view, then used that for the view. This is
> > horribly inefficient, and results in a lot of people thinking views
> > are slow. Not sure if this has been addressed in MySQL yet, don't
> > really care anymore, since I rarely use mysql for anything anymore.
>
> Some simple experiments with mysql 5.0.67 suggest that this meme is
> obsolete there too. I found some cases where it looks like we optimize
> a bit better than they do, but for simple views you seem to get the
> same plan as if you'd written out the equivalent query in-line.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

When your life is on the go—take your life with you. Try Windows Mobile® today

On Sun, Nov 2, 2008 at 7:40 PM, Martin Gainty <mgainty@hotmail.com> wrote:
> does anyone  know if postgres support 'refresh' of applicable index(es) of a
> materialized view on refresh?

Postgresql has no built in support for materialized views.  If you
follow the excellent tutorial on how to make your own, you window up
creating real tables to hold the data, and any indexes you create on
those tables will be dynamically updated when the materialized view is
updated.

Tom Lane wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
>> Note that, at least in older versions, MySQL completely materialized a
>> temporary table from a view, then used that for the view.  This is
>> horribly inefficient, and results in a lot of people thinking views
>> are slow.  Not sure if this has been addressed in MySQL yet, don't
>> really care anymore, since I rarely use mysql for anything anymore.
>
> Some simple experiments with mysql 5.0.67 suggest that this meme is
> obsolete there too.  I found some cases where it looks like we optimize
> a bit better than they do, but for simple views you seem to get the
> same plan as if you'd written out the equivalent query in-line.
>

This is true of simple queries, but as soon as your query starts
becoming "complex" (e.g. involving subselects) it just dumps the result
of the view query into a temporary table and uses that. (Tested with
5.0.67 too).