Re: Calculating 95th percentiles

From: marcin mank <marcin(dot)mank(at)gmail(dot)com>
To: Landreville <landreville(at)deadtreepages(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Calculating 95th percentiles
Date: 2011-03-06 00:34:44
Message-ID: AANLkTikbsQucnq4zVdCftZvWHw+9wj-cek=V6dES0320@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Omar Kilani 2011-03-06 00:38:08 Re: Linux I/O schedulers - CFQ & random seeks
Previous Message Tobias Brox 2011-03-05 10:09:55 Re: Table partitioning