Re: Calculating 95th percentiles

On Sat, Mar 5, 2011 at 7:34 PM, marcin mank <marcin(dot)mank(at)gmail(dot)com> wrote:
> Is this fast enough on a slave:
>
>
> with deltas as (select * from get_delta_table(...)),
> p95 as(select round(count(volume_id) * 0.95) as p95v from deltas)
> select
> (select in_rate from deltas, p95 where
> in_rate_order = p95v),
> (select out_rate from deltas, p95 where
> out_rate_order = p95v)
> etc..
> Greetings
> Marcin
>

I really didn't know you could use a with statement on a read-only
database -- I don't think I even knew the with statement existed in
Postgres (is it documented somewhere?). I will try this out.

I am also looking at Pierre's suggestion of calculating the delta
value on insert. To do this I am going to update all the rows
currently in the partitioned tables. Does anyone know if this will
still use constraint exclusion in the correlated subquery or will it
scan every partitioned table for each updated row?:

update volume
set in_delta = in_octets - vprev.in_octets,
out_delta = out_octets - vprev.out_octets
from volume vprev
where vprev.insert_timestamp =
(select max(insert_timestamp) from volume v
where v.switch_port_id = volume.switchport_id
and v.insert_timestamp < volume.insert_timestamp);

I suppose I can check with an analyze before I execute it (I still
have to alter the table to add the delta columns).

Thanks,

Landreville