Re: Tuning to speed select

On Wed, 2006-08-09 at 14:58, louis gonzales wrote:
> I'm not so sure about that, when you create a view on a table - at least
> with Oracle - which is a subset(the trivial or 'proper' subset is the
> entire table view) of the information on a table, when a select is
> issued against a table, Oracle at least, determines if there is a view
> already on a the table which potentially has a smaller amount of
> information to process - as long as the view contains the proper
> constraints that meet your 'select' criteria, the RDBMS engine will have
> fewer records to process - which I'd say, certainly constitutes a time
> benefit, in terms of 'performance gain.'
>
> Hence my reasoning behind determining IF there is a subset of the 'big
> table' that is frequented, I'd create a view on this, assuming
> postgresql does this too? Maybe somebody else can answer that for the
> pgsql-general's general information?
>
> query-speed itself is going to be as fast/slow as your system is
> configured for, however my point was to shave some time off of a 1M+
> record table, but implementing views of 'frequently' visisted/hit
> records meeting the same specifications.

There are basically two ways to do views. The simple way, is to have a
view represent a query that gets run everytime you call it. The more
complex way is to "materialize" the view data, and put it into a new
table, and then update that table whenever the source table changes.

PostgreSQL has native support for the first type. They're cheap and
easy, and work for most of the things people need views for (i.e. hiding
complexity).

PostgreSQL is extensible, and therefore you can institute the second
type (i.e. materialized views) on your own. Thanksfully, someone else
has already done most of the work for us, by the name of Jonathan
Gardner, and you can find his nifty guide by typing "materialized views
postgresql" into google.

Gardner's materialized views support several update methods depending on
what you need from your mat views. It's also a danged fine tutorial on
how to write some simple plpgsql functions.