From: PFC <lists@boutiquenumerique.com>
To: Amit V Shah <ashah@tagaudit.com>
To: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: Re: Do Views execute underlying query everytime ??
Date: Tue, 21 Jun 2005 17:00:10 +0200
Message-ID: <op.ssp6mkndth1vuj@localhost> (raw)
In-Reply-To: <0C072E7CC947D511AC9600A0CC7341200256CF51@vpn.tagaudit.com>
References: <0C072E7CC947D511AC9600A0CC7341200256CF51@vpn.tagaudit.com>
> However, I donot know if the query that creates the view is executed
> everytime I select something from the view. Because if that is the case,
> then I think my queries will again be slow. But if that is the way views
> work, then what would be the point in creating them ..
Views are more for when you have a query which keeps coming a zillion
time in your application like :
SELECT p.*, pd.* FROM products p, products_names pd WHERE p.id=pd.id AND
pd.language=...
You create a view like :
CREATE VIEW products_with_name AS SELECT p.*, pd.* FROM products p,
products_names pd WHERE p.id=pd.id
And then you :
SELECT * FROM products_with_name WHERE id=... AND language=...
It saves a lot of headache and typing over and over again the same thing,
and you can tell your ORM library to use them, too.
But for your application, they're useless, You should create a
"materialized view"... which is just a table and update it from a CRON job.
You can still use a view to fill your table, and as a way to hold your
query, so the cron job doesn't have to issue real queries, just filling
tables from views :
CREATE VIEW cached_stuff_view AS ...
And once in while :
BEGIN;
DROP TABLE cached_stuff;
CREATE TABLE cached_stuff AS SELECT * FROM cached_stuff_view;
CREATE INDEX ... ON cached_stuff( ... )
COMMIT;
ANALYZE cached_stuff;
Or :
BEGIN;
TRUNCATE cached_stuff;
INSERT INTO cached_stuff SELECT * FROM cached_stuff_view;
COMMIT;
ANALYZE cached_stuff;
If you update your entire table it's faster to just junk it or truncate it
then recreate it, but maybe you'd prefer TRUNCATE which saves you from
having to re-create of indexes... but it'll be faster if you drop the
indexes and re-create them afterwards anyway instead of them being updated
for each row inserted. So I'd say DROP TABLE.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, ashah@tagaudit.com
Subject: Re: Do Views execute underlying query everytime ??
In-Reply-To: <op.ssp6mkndth1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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