Re: view or index to optimize performance

On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote:
> I thought that if I used a view to retrieve data its content might be cached
> so it would make the query faster.

No. A view is essentially exactly the same as inserting the view
definition into the query that's using it. IE:

CREATE VIEW v AS SELECT * FROM t;

SELECT * FROM v becomes:

SELECT * FROM (SELECT * FROM t) v;

What you could do is partition the table so that critical information is
stored in a smaller table while everything else goes to a larger table.
You can then do a UNION ALL view on top of that to 'glue' the two tables
together. You can even define rules so that you can do updates on the
view. http://cvs.distributed.net/viewcvs.cgi/stats-sql/logdb/ has an
example that's similar to this. Note that you'll need an appropriate
index on the large table so that PostgreSQL can quickly tell it doesn't
contain values that are in the small table. Or, in 8.1 you could use a
constraint. You could also do this with inherited tables instead of
views.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461