Changeset a7da22e2c7fcc5e15bb1a4e4ab695d6a0c560b43

Show
Ignore:
Timestamp:
05/14/08 20:10:38 (10 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1210795838 +0000
git-parent:

[2af2d1e9f3a5f328366da7a13806fa087eaafda7]

git-author:
Denish Patel <denish@omniti.com> 1210795838 +0000
Message:

Added date_trunc('hour',rollup_time) index on 20m rollup tables so we can use during 1 hour rollup_table function.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/schema.sql

    rbe8707f ra7da22e  
    8080    PRIMARY KEY (rollup_time,sid,name) 
    8181); 
     82 
     83CREATE OR REPLACE FUNCTION stratcon.date_hour(timestamptz)  
     84 RETURNS timestamptz as $BODY$ 
     85 SELECT date_trunc('hour',$1); 
     86 $BODY$  
     87 language 'sql'  
     88 IMMUTABLE STRICT; 
     89 
     90CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time  
     91              ON stratcon.rollup_matrix_numeric_20m(date_hour(rollup_time)); 
    8292 
    8393CREATE TABLE stratcon.rollup_matrix_numeric_60m( 
     
    562572   
    563573    FOR rec IN  
    564                 SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     574                SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    565575                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    566576                         FROM stratcon.rollup_matrix_numeric_20m 
    567                            WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
    568                    GROUP BY date_trunc('hour',rollup_time),sid,name 
     577                           WHERE date_hour(rollup_time)= v_min_whence 
     578                   GROUP BY date_hour(rollup_time),sid,name 
    569579        LOOP 
    570580