pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Do Views offer any performance advantage?
5+ messages / 3 participants
[nested] [flat]

* Do Views offer any performance advantage?
@ 2003-04-15 15:49  Robert John Shepherd <robert@reviewer.co.uk>
  0 siblings, 1 reply; 5+ messages in thread

From: Robert John Shepherd @ 2003-04-15 15:49 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

Whilst I often use views for convenience, is there any performance
advantage at all in using a view rather than running the same query
directly on the tables themselves?

Is it quicker to run a full complex query rather than run a much simpler
query on a view? (Hope that makes sense)


Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk  




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Do Views offer any performance advantage?
@ 2003-04-15 17:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Robert John Shepherd <robert@reviewer.co.uk>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2003-04-15 17:26 UTC (permalink / raw)
  To: Robert John Shepherd <robert@reviewer.co.uk>; +Cc: pgsql-performance

"Robert John Shepherd" <robert@reviewer.co.uk> writes:
> Whilst I often use views for convenience, is there any performance
> advantage at all in using a view rather than running the same query
> directly on the tables themselves?

No, a view is just a macro.

There is probably some minuscule cost difference involved --- you save
parsing and parse analysis of a long query string.  On the other hand,
you pay to pull the view definition from the catalogs and merge it into
the given query.  I'd not care to hazard a guess on whether the actual
net cost is more or less; but in any case these costs will be swamped
by query planning and execution, if the query is complex.

If you're concerned about reducing parse/plan overhead for repetitive
queries, the prepared-statement facility (new in 7.3) is what to look
at.  Views won't do much for you.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Do Views offer any performance advantage?
@ 2003-04-15 19:55  Andreas Pflug <Andreas.Pflug@web.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Andreas Pflug @ 2003-04-15 19:55 UTC (permalink / raw)
  To: pgsql-performance

Tom Lane wrote:

>
>There is probably some minuscule cost difference involved --- you save
>parsing and parse analysis of a long query string.  On the other hand,
>you pay to pull the view definition from the catalogs and merge it into
>the given query.  I'd not care to hazard a guess on whether the actual
>net cost is more or less; but in any case these costs will be swamped
>by query planning and execution, if the query is complex.
>
Actually, there are cases when a view can impact performance.
If you are joining a view, it seems to be treated as a subquery, that 
might have a much larger result than you would like.

Imagine
SELECT something
  FROM A JOIN B JOIN C ...
 WHERE A.primaryKeyFoo=1234 ...

 where C is a view, containing JOINs itself, I observed a query plan 
(7.3.2) like
A JOIN B JOIN (D JOIN E)
instead of
A JOIN B JOIN D JOIN E which would be much more efficient for the 
A.primaryKeyFoo restriction.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Do Views offer any performance advantage?
@ 2003-04-15 23:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andreas Pflug <Andreas.Pflug@web.de>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2003-04-15 23:37 UTC (permalink / raw)
  To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-performance

Andreas Pflug <Andreas.Pflug@web.de> writes:
> Actually, there are cases when a view can impact performance.
> If you are joining a view, it seems to be treated as a subquery, that 
> might have a much larger result than you would like.

> Imagine
> SELECT something
>   FROM A JOIN B JOIN C ...
>  WHERE A.primaryKeyFoo=1234 ...

>  where C is a view, containing JOINs itself, I observed a query plan 
> (7.3.2) like
> A JOIN B JOIN (D JOIN E)
> instead of
> A JOIN B JOIN D JOIN E which would be much more efficient for the 
> A.primaryKeyFoo restriction.

This is not the view's fault though --- the same would have happened
if you'd written explicitly

	FROM A JOIN B JOIN (D JOIN E)

7.4 will be less rigid about this (with or without a view ...)

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Do Views offer any performance advantage?
@ 2003-04-16 08:45  Andreas Pflug <Andreas.Pflug@web.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Andreas Pflug @ 2003-04-16 08:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance

Tom Lane wrote:

>This is not the view's fault though --- the same would have happened
>if you'd written explicitly
>
>	FROM A JOIN B JOIN (D JOIN E)
>
That's right, I just wanted to warn about accessive use of joins with 
views. I noticed this in an application, where quite big views where 
joined for convenience, and the result wasn't satisfying.

>
>7.4 will be less rigid about this (with or without a view ...)
>
Good!
Regards,
Andreas




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-04-16 08:45 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-15 15:49 Do Views offer any performance advantage? Robert John Shepherd <robert@reviewer.co.uk>
2003-04-15 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-15 19:55   ` Andreas Pflug <Andreas.Pflug@web.de>
2003-04-15 23:37     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-16 08:45       ` Andreas Pflug <Andreas.Pflug@web.de>

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