Hi all,

I have like a repository table with is very very huge with atleast a few
hundreds of millions, may be over that. The information is stored in form of
rows in these tables. I need to make that information wide based on some
grouping and display them as columns on the screen.

I am thinking of having a solution where I create views for each screen,
which are just read only.

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 ..

Any suggestions, helps --

(Please pardon if this question should not be on performance forum)

Thanks,
Amit


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 6/21/05, Amit V Shah <ashah@tagaudit.com> wrote:
> Hi all,
....
> I am thinking of having a solution where I create views for each screen,
> which are just read only.
>
> 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 ..
>
> Any suggestions, helps --

They do get executed every time. I have a similar issue, but my data
does not change very frequently, so instead of using a view, I create
lookup tables to hold the data. So once a day I do something like
this:
drop lookup_table_1;
create table lookup_table_1 as SELECT ...;

In my case, rows are not deleted or updated, so I don't actually do a
"drop table..." I merely add new records to the existing table, but if
your data changes, the drop table technique can be faster than doing a
delete or update.

--
Matthew Nuzum
www.bearfruit.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Amit V Shah wrote:
> Hi all,
>
> I have like a repository table with is very very huge with atleast a few
> hundreds of millions, may be over that. The information is stored in form of
> rows in these tables. I need to make that information wide based on some
> grouping and display them as columns on the screen.
>
> I am thinking of having a solution where I create views for each screen,
> which are just read only.
>
> 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 ..

That's exactly how they work. You'd still want them because they let you
simplify access control (user A can only see some rows, user B can see
all rows) or just make your queries simpler.

Sounds like you want what is known as a "materialised view" which is
basically a summary table that is kept up to date by triggers. You query
the table instead of actually recalculating every time. Perhaps google
for "postgresql materialized view" (you might want a "z" or "s" in
materialised).

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> 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.












---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org