Re: [PERFORM] Help with tuning this query (with explain analyze finally)

[John A Meinel]

John Arbash Meinel wrote:

Ken wrote:

Richard,

What do you mean by summary table?  Basically a cache of the query
into a table with replicated column names of all the joins?  I'd
probably have to whipe out the table every minute and re-insert the
data for each carrier in the system.  I'm not sure how expensive this
operation would be, but I'm guessing it would be fairly heavy-weight.
And maintaince would be a lot harder because of the duplicated
columns, making refactorings on the database more error-prone.  Am I
understanding your suggestion correctly? Please correct me if I am.

Can you turn the problem around? Calculate what you want for all
users (once every 60 seconds) and stuff those results into a summary
table. Then let the users query the summary table as often as they
like (with the understanding that the figures aren't going to update
any faster than once a minute)

It's the same idea of a materialized view, or possibly just a lazy cache.

...

This unrolls all of the work, a table which should be really fast to
query. If this query takes less than 10s to generate, than just have a
service run it every 60s. I think for refreshing, it is actually faster
to drop the table and recreate it, rather than deleteing the entries.
Dropping also has the advantage that if you ever add more rows to s or
ss, then the table automatically gets the new entries.

Just as a small update. If completely regenerating the cache takes to long, the other way to do it, is to create insert and update triggers on s and ss, such that as they change, they also update the cachedview table.

Something like

CREATE TRIGGER on_ss_ins AFTER INSERT ON ss FOR EACH ROW EXECUTE
INSERT INTO cached_view SELECT p.id as person_id, s.*, ss.* FROM <the big stuff> WHERE s.id = NEW.id;

This runs the same query, but notice that the WHERE means it only allows the new row. So this query should run fast. It is a little bit of overhead on each of your inserts, but it should keep the cache up-to-date. With something like this, I would have the final client query still include the date restriction, since you accumulate older rows into the cached view. But you can run a daily process that prunes out everything older than 31 days, which keeps the cachedview from getting really large.

John
=:->

signature.asc

Description: OpenPGP digital signature