From what you say I understand that you have a huge table like this :

( name, value, id )

And you want to make statistics on (value) according to (name,id).


First of all a "materialized view" doen't exist in postgres, it's just a word to name "a table automatically updated by triggers".
An example would be like this :

table orders (order_id, ...)
table ordered_products (order_id, product_id, quantity, ...)

If you want to optimize the slow request :
"SELECT product_id, sum(quantity) as total_quantity_ordered
FROM ordered_products GROUP BY product_id"

You would create a cache table like this :
table ordered_products_cache (product_id, quantity)

And add triggers ON UPDATE/INSERT/DELETE on table ordered_products to update ordered_products_cache accordingly.

Of course in this case everytime someone touches ordered_products, an update is issued to ordered_products_cache.


In your case I don't think that is the solution, because you do big updates. With triggers this would mean issuing one update of your materialized view per row in your big update. This could be slow.

In this case you might want to update the cache table in one request rather than doing an awful lot of updates.

So you have two solutions :

1- Junk it all and rebuild it from scratch (this can be faster than it seems) 2- Put the rows to be added in a temporary table, update the cache table considering the difference between this temporary table and your big table, then insert the rows in the big table.

This is the fastest solution but it requires a bit more coding (not THAT much though).


As for the structure of your cache table, you want :

Screen 1 -
Stat1      Stat2        Stat3
Value      Value        Value
Value      Value        Value

Screen 2 -
Stat3      Stat1        Stat5
Value      Value        Value
Value      Value        Value

You have several lines, so what is that ? is it grouped by date ? I'll presume it is.

So your screens basically show a subset of :

SELECT date, name, sum(value) FROM table GROUP BY name, date

This is what you should put in your summary table.
Then index it on (date,name) and build your screens with :

SELECT * FROM summary WHERE (date BETWEEN .. AND ..) AND name IN (Stat3, Stat1, Stat5)

That should be pretty easy ; you get a list of (name,date,value) that you just have to format accordingly on your screen.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Reply via email to