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 ########################################## 2010/1/10 Scott Marlowe <scott.marl...@gmail.com> > On Sat, Jan 9, 2010 at 2:46 PM, Andreas Kretschmer > <akretsch...@spamfence.net> wrote: > > Stefan Kaltenbrunner <ste...@kaltenbrunner.cc> wrote: > > > >> Andreas Kretschmer wrote: > >>> zxo102 ouyang <zxo...@gmail.com> wrote: > >>> > >>>> Hi everyone, I am using postgresql 8.3-beta3. I have a table > >>>> 'test' with three fields: > >>> > >>> I'm guessing you mean 8.4-beta3, right? > >> > >> either of those are unsuitable for any kind of production use... > > > > Hey, we needs beta-testers, right? And yes, read again, the table is > > called 'test' ... > > True, but if you're gonna test betas / alphas, I'd think 8.5 alpha > would be the choice for testing. 8.4's beta ended quite some time > ago. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >