Postgres Pro
Downloads
Home   >   mailing lists

Thread: How efficient are Views

How efficient are Views

From
John Taylor
Date:
Hi,

I'm thinking about using a view for my application.

However, I'm wondering about how efficient it will be once I have a large amount of data
loaded.

Are views generated for every access ?
Are they generated when the underlying data changes ?

Whould it maybe be faster to have a seperate table alongside the base data, and
generate it via a trigger or stored procedure ?

Thanks
JohnT

Re: How efficient are Views

From
"Henshall, Stuart - WCP"
Date:

A view just re-executes the query.
Therefore a view is as efficient as the query it's based upon :)
hth,
- Stuart

> -----Original Message-----
> From: John Taylor [mailto:postgres@jtresponse.co.uk]
>
> Hi,
>
> I'm thinking about using a view for my application.
>
> However, I'm wondering about how efficient it will be once I
> have a large amount of data
> loaded.
>
> Are views generated for every access ?
> Are they generated when the underlying data changes ?
>
> Whould it maybe be faster to have a seperate table alongside
> the base data, and
> generate it via a trigger or stored procedure ?
>
> Thanks
> JohnT

Re: How efficient are Views

From
Tom Lane
Date:
John Taylor <postgres@jtresponse.co.uk> writes:
> I'm thinking about using a view for my application.

> However, I'm wondering about how efficient it will be once I have a
> large amount of data loaded.

Views are effectively just query macros --- Postgres doesn't have any
notion of pre-materialized views.

Whether this will be efficient or not depends very much on what the
intended view is and how you use it.

> Whould it maybe be faster to have a seperate table alongside the base
> data, and generate it via a trigger or stored procedure ?

You could do that, and in some cases it'd be helpful; in other cases
it'd be a big loss.  Without any details on your scenario, we really
can't say more than that.  What's the proposed view?  How often will
you access the view as compared to modifying the underlying table?

            regards, tom lane

Re: How efficient are Views

From
"Henshall, Stuart - WCP"
Date:

Everytime the view is accessed the query is executed.
- Stuart

> -----Original Message-----
> From: John Taylor [mailto:postgres@jtresponse.co.uk]
>
> On Wednesday 12 June 2002 16:21, you wrote:
>
> > > A view just re-executes the query.
> > Therefore a view is as efficient as the query it's based upon :)
> > hth,
>
> But, does the query get processed, every time the view is
> accessed, or whenever
> the underlying data is changed ?
>
> If it is the former, then it would be the same as using a subselect:
> SELECT * FROM (SELECT my view query) WHERE blah ...
>
> Thanks
> JohnT
>

Re: How efficient are Views

From
Andrew McMillan
Date:
On Thu, 2002-06-13 at 03:02, John Taylor wrote:
> Hi,
>
> I'm thinking about using a view for my application.
>
> However, I'm wondering about how efficient it will be once I have a large amount of data
> loaded.
>
> Are views generated for every access ?
> Are they generated when the underlying data changes ?
>
> Whould it maybe be faster to have a seperate table alongside the base data, and
> generate it via a trigger or stored procedure ?

It depends on your ratio of queries to inserts.  It depends on the size
of your tables and the indexing that you have on them.  It depends on
whether your queries are on subsets or summaries.

On the other hand, there is no reason why queries should necessarily be
slow 'once you have a large amount of data loaded' - that depends on a
lot more database choices than just whether you use a view.

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?