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

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(dot)marlowe(at)gmail(dot)com>

> On Sat, Jan 9, 2010 at 2:46 PM, Andreas Kretschmer
> <akretschmer(at)spamfence(dot)net> wrote:
> > Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> wrote:
> >
> >> Andreas Kretschmer wrote:
> >>> zxo102 ouyang <zxo102(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>