actual time=8163.890..8163.893 means
Initializing that step ("startup") took 8163.890ms
Running the whole step took 8163.893ms
So in that case nearly the complete work was done in the startup phase of that step.
Edit:
The same logic is "applied" to the cost information
cost=2928781.21..2929243.02 means:
The cost to initialize this step was estimated at: 2928781.21
The cost to perform the step was estimated at: 2929243.02
(note that "cost" does not have a unit - it's an arbitrary value)
This is also explained here:
http://www.postgresql.org/docs/current/static/using-explain.html
This was hard! I don't know if this is simpler, but at least it doesn't use window function nor produce rows that require being filtered out.
with recursive r(k, n) as (
with t(k) as (values (1),(2),(3),(4),(5)) -- the data we want to filter
-- with t(k) as (values (1),(5),(7),(10),(11),(12),(13))
-- with t(k) as (values (6),(8),(11),(16),(20),(23))
-- with t(k) as (values (6),(8),(12),(16),(20),(23))
,t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t) -- precalculate what's next
select * from (select * from t2 limit 1) x -- limit 1 directly fails in a union!
UNION ALL
select t2.* from r, t2 where t2.k = r.n -- on each iteration, keep only the value that matches the previous precalculated next one
)
select k from r
Testing
This alternative seems to be less efficient for very small sets, but more or less linear in performance, whilst the original seems to be exponentially more sluggish.
drop table if exists t;
create temp table t(k) AS
with recursive r(n) as (
select floor(random()*10)::int + 1
UNION ALL
select n + floor(random()*10)::int + 1
from r
where n < 100000) -- change to increase or reduce set
select * from r; -- surprisingly fast! Go PG!
create index on t(k);
with recursive r(n, pri) as (
select min(k), 1::bigint from t
UNION
select k, (rank() over(order by k)) rr
from r, t
where t.k >= r.n+5 and r.pri = 1
)
select count(*) from r where pri = 1; -- I aborted it after waiting for a minute
with recursive r(k, n) as (
with t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t)
select * from (select * from t2 limit 1) x
UNION ALL
select t2.* from r, t2 where t2.k = r.n
)
select count(*) from r -- 26" in my server
Best Answer
Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.
Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to
round()to two fractional digits I suggestnumeric(which is the same asdecimalin Postgres).It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to
numericautomatically because of the decimal point (100.0).It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (
count(refinst) * 100) can be computed with cheap and exactintegerarithmetic. Only then we cast tonumericand divide by the nextinteger(which we do not cast additionally).NULLIF(count(*), 0)prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.Rounded to two fractional digits: