Re: Calculating 95th percentiles

On Fri, Mar 4, 2011 at 4:18 PM, Landreville
<landreville(at)deadtreepages(dot)com> wrote:

>    create temporary table deltas on commit drop as
>        select * from get_delta_table(p_switchport_id, p_start_date,
> p_end_date);
>
>    select round(count(volume_id) * 0.95) into v_95th_row from deltas;
>    select in_rate into v_record.in_95th from deltas where
> in_rate_order = v_95th_row;
>    select out_rate into v_record.out_95th from deltas where
> out_rate_order = v_95th_row;
>    select sum(in_delta), sum(out_delta) into v_record.in_total,
> v_record.out_total from deltas;
>
> Unfortunately using a temporary table means that I cannot run this
> query on the read-only slave, but I can't see a way around using one.

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