Re: An issue with max() and order by ... limit 1 in postgresql8.3-beta3 - Mailing list pgsql-general

Thanks for your guys' help. I did not know the meaning of betas / alphas things before and just simply downloaded one to use. Now my 8.3 -beta3 version is really in production and get 100 rows of monitoring data per minutes. So far so good. Anyway, I will upgrade it to a latest stable version. 

With Andreas's example query:

    select distinct on (sid) sid, data, date from test order by sid, data desc, date;

I rewrite my query. It just take 20 seconds to finish the search and is much better than  my old query which takes 400 seconds to return the results. Thanks again for Andreas's example query.   For reference, the following is my new query (I create an index for two fileds (rd.sensor_id,rd.sensor_channel)).

 

Thanks  a lot gain.

 

Ouyang

 

#############################################

select rt_data.r_flowmeter_caliber as  r_flowmeter_caliber,
       rt_data.r_max01_sloc as r_max01_sloc,
       rt_data.r_max01_sdata as r_max01_sdata,
       rt_data.r_max01_sdate as r_max01_sdate,
       rt_data.r_min01_sdata as r_min01_sdata,
       rt_data.r_min01_sdate as r_min01_sdate,
       rt_data.r_avg01_sdata as r_avg01_sdata,
       acc_data.r_end_sdate as r_end_sdate,
       acc_data.r_end_sdata as r_end_sdata,
       acc_data.r_start_sdate as r_start_sdate,
       acc_data.r_start_sdata as r_start_sdata,
       acc_data.r_acc_sdata as r_acc_sdata
       from ( select ec.flowmeter_caliber as r_flowmeter_caliber,
         max01.r_sloc as r_max01_sloc,
         round(max01.r_sdata*100)/100 as r_max01_sdata,
         max01.r_sdate as r_max01_sdate,
         round(min01.r_sdata*100)/100 as r_min01_sdata,
         min01.r_sdate as r_min01_sdate,
         round(avg01.r_sdata*100)/100 as r_avg01_sdata,
         max01.r_channel as r_channel,
         max01.r_sid as r_sid,
         max01.r_sloc as r_sloc
         from (select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid,
                                 rd.sensor_channel as r_channel,
                          rd.sensor_data as r_sdata,
                          rd.sensor_date as r_sdate,
                                 sc.external_ins as r_sloc 
                              from record_data rd, sensor_cfg sc, energy_classification02 ec 
                              where   rd.sensor_date between '2009-08-01' and '2010-01-08' and
                                 sc.sensor_id = rd.sensor_id and
                               sc.external_ins=ec.measure_name and
                               sc.channel = ec.instantaneous_channel and
                               sc.channel = rd.sensor_channel and
                               sc.remarks='瞬时值' and
                               ec.flowmeter_caliber='流量'  
                             order by rd.sensor_id,rd.sensor_channel, rd.sensor_data DESC, rd.sensor_date
               ) max01,
         ( select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid,
                                 rd.sensor_channel as r_channel,
                          rd.sensor_data as r_sdata,
                          rd.sensor_date as r_sdate,
                                 sc.external_ins as r_sloc 
                              from record_data rd, sensor_cfg sc, energy_classification02 ec 
                              where   rd.sensor_date between '2009-08-01' and '2010-01-08' and
                                 sc.sensor_id = rd.sensor_id and
                               sc.external_ins=ec.measure_name and
                               sc.channel = ec.instantaneous_channel and
                               sc.channel = rd.sensor_channel and
                               sc.remarks='瞬时值' and
                               ec.flowmeter_caliber='流量'  
                             order by rd.sensor_id,rd.sensor_channel, rd.sensor_data ASC, rd.sensor_date
          ) min01,
         ( select avg(rd01.sensor_data) as r_sdata,
           rd01.sensor_id as r_sid,
           rd01.sensor_channel as r_channel
           from record_data rd01,
           sensor_cfg sc,
           energy_classification02 ec
           where rd01.sensor_date between '2009-08-01' and '2010-01-08' and
           sc.sensor_id = rd01.sensor_id and
           sc.external_ins=ec.measure_name and
           sc.channel = ec.instantaneous_channel and
           sc.channel=rd01.sensor_channel and
           sc.remarks='瞬时值' and
           ec.flowmeter_caliber='流量'
           group by rd01.sensor_id,rd01.sensor_channel
           ) avg01,
         energy_classification02 ec,
         sensor_cfg sc
         where  max01.r_sid=min01.r_sid and
         min01.r_sid=avg01.r_sid and
         max01.r_sid=sc.sensor_id and
         sc.channel = ec.instantaneous_channel and
         sc.channel= min01.r_channel and
         sc.channel= max01.r_channel and
         sc.channel=avg01.r_channel and
         sc.external_ins=ec.measure_name and
         sc.remarks='瞬时值' and
         ec.flowmeter_caliber='流量'
                ) rt_data,
         (select round(max01.r_sdata-min01.r_sdata)*100/100 as r_acc_sdata,
          max01.r_sid as r_sid,
          max01.r_sloc as r_sloc,
          max01.r_sdate as r_end_sdate,
          max01.r_sdata as r_end_sdata,
          min01.r_sdate as r_start_sdate,
          min01.r_sdata as r_start_sdata
          from
         (select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid,
                                 rd.sensor_channel as r_channel,
                          rd.sensor_data as r_sdata,
                          rd.sensor_date as r_sdate,
                                 sc.external_ins as r_sloc 
                              from record_data rd, sensor_cfg sc, energy_classification02 ec 
                              where   rd.sensor_date between '2009-08-01' and '2010-01-08' and
                                 sc.sensor_id = rd.sensor_id and
                               sc.external_ins=ec.measure_name and
                               sc.channel = ec.cumulative_channel and
                               sc.channel = rd.sensor_channel and
                               sc.remarks='累积值' and
                               ec.flowmeter_caliber='流量'  
                             order by rd.sensor_id,rd.sensor_channel, rd.sensor_data DESC, rd.sensor_date
               ) max01,
         (select distinct on (rd.sensor_id,rd.sensor_channel) rd.sensor_id as r_sid,
                                 rd.sensor_channel as r_channel,
                          rd.sensor_data as r_sdata,
                          rd.sensor_date as r_sdate,
                                 sc.external_ins as r_sloc 
                              from record_data rd, sensor_cfg sc, energy_classification02 ec 
                              where rd.sensor_date between '2009-08-01' and '2010-01-08' and
                                 sc.sensor_id = rd.sensor_id and
                               sc.external_ins=ec.measure_name and
                               sc.channel = ec.cumulative_channel and
                               sc.channel = rd.sensor_channel and
                               sc.remarks='累积值' and
                               ec.flowmeter_caliber='流量'  
                             order by rd.sensor_id,rd.sensor_channel, rd.sensor_data ASC, rd.sensor_date
                 ) min01,
          energy_classification02 ec,
          sensor_cfg sc
          where  max01.r_sid=min01.r_sid and
          max01.r_sid=sc.sensor_id and
          sc.channel = ec.cumulative_channel and
          sc.channel= min01.r_channel and
          sc.channel=max01.r_channel and
          sc.external_ins=ec.measure_name and
          sc.remarks='累积值' and
          ec.flowmeter_caliber='流量'
          ) acc_data
          where acc_data.r_sloc = rt_data.r_sloc
          order by  r_max01_sloc desc

##########################################