SEO

After I sent out this email, I found this article from google

http://jonathangardner.net/PostgreSQ.../matviews.html

Looks like we can control as to when the views refresh... I am still kind of
confused, and would appreciate help !!

The create/drop table does sound a solution that can work, but the thing is
I want to get manual intervention out, and besides, my work flow is very
complex so this might not be an option for me :-(

Thanks,
Amit

-----Original Message-----
From: Matthew Nuzum [mailto:mattnuzum@gmail.com]
Sent: Tuesday, June 21, 2005 10:45 AM
To: Amit V Shah
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Do Views execute underlying query everytime ??


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 6: Have you searched our list archives?

http://archives.postgresql.org

Amit V Shah wrote:

>After I sent out this email, I found this article from google
>
>http://jonathangardner.net/PostgreSQ.../matviews.html
>
>Looks like we can control as to when the views refresh... I am still kind of
>confused, and would appreciate help !!
>
>The create/drop table does sound a solution that can work, but the thing is
>I want to get manual intervention out, and besides, my work flow is very
>complex so this might not be an option for me :-(
>
>Thanks,
>Amit
>

Just to make it clear, a view is not the same as a materialized view.
A view is just a set of rules to the planner so that it can simplify
interactions with the database. A materialized view is a query which has
been saved into a table.

To set it up properly, really depends on what your needs are.

1. How much time can elapse between an update to the system, and an
update to the materialized views?
2. How many updates / (sec, min, hour, month) do you expect. Is
insert performance critical, or secondary.

For instance, if you get a lot of updates, but you can have a 1 hour lag
between the time a new row is inserted and the view is updated, you can
just create a cron job that runs every hour to regenerate the
materialized view.

If you don't get many updates, but you need them to show up right away,
then you can add triggers to the affected tables, such that
inserting/updating to a specific table causes an update to the
materialized view.

There are quite a few potential tradeoffs. Rather than doing a
materialized view, you could just improve your filters. If you are doing
a query to show people the results, you generally have some sort of
upper bound on how much data you can display. Humans don't like reading
more than 100 or 1000 rows. So create your normal query, and just take
on a LIMIT 100 at the end. If you structure your query properly, and
have appropriate indexes, you should be able to make the LIMIT count,
and allow you to save a lot of overhead of generating rows that you
don't use.

I would probably start by posting the queries you are currently using,
along with an EXPLAIN ANALYZE, and a description of what you actually
need from the query. Then this list can be quite helpful in
restructuring your query to make it faster.

John
=:->


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCuCu5JdeBCYSNAAMRAtbMAJ9MsgkcLLiAacWYjlWnT3 56mVmvcQCgocRv
N1bGrwwkpZ/+DQ799UUxnJA=
=86jf
-----END PGP SIGNATURE-----