I have a particles table in PostgreSQL 10.19 that looks like this:
CREATE TABLE particles (
particle_diameter REAL,
particle_speed REAL
);
INSERT INTO particles VALUES
(0.35, 0.74),
(0.57, 2.63),
(0.27, 1.05),
(0.65, 2.33);
What I want is to aggregate my data by diameter range (i.e. each particles that have a diameter between for example 0 and 0.2 mm, 0.2 and 0.4 mm, etc.) and speed range. I want to generate a serie of diameter range, and a serie of speed range and finally count the number of particles for each duo diameter range and speed range.
So far I managed to reach the desired result using this query:
WITH speed_series AS (
SELECT generate_series(-1, 19.8, 0.2) AS speed_from
), speed_range AS (
SELECT speed_from, (speed_from + 0.2) AS speed_to FROM speed_series
), diameter_series AS (
SELECT generate_series(0, 9.8, 0.2) AS diameter_from
), diameter_range AS (
SELECT
diameter_from, (diameter_from + 0.2) AS diameter_to, speed_from, speed_to
FROM diameter_series, speed_range
)
SELECT
diameter_from,
diameter_to,
speed_from,
speed_to,
(SELECT
COUNT(particle_diameter)
FROM particles
WHERE particle_diameter BETWEEN diameter_from AND diameter_to
AND particle_speed BETWEEN speed_from AND speed_to
)
FROM diameter_range;
You can explore it on this : db<>fiddle
On a relatively small dataset (~30k records) this query took more than a minute to execute. So my question is:
Is there a way to rewrite this query to be more efficient and less time consuming?
I would try width_bucket() or floor() here:
with b as (
select width_bucket(particle_diameter, 0, 10, 50) pd,
width_bucket(particle_speed, -1, 20, 105) ps
from particles)
select pd * .2 - .2 diam_from, pd * .2 diam_to,
ps * .2 - 1.2 speed_from, ps * .2 - 1 speed_to,
count(1) cnt
from b group by pd, ps
Of course you can join with generated series if you need these zero-rows:
with b as (
select width_bucket(particle_diameter, 0, 10, 50) pd,
width_bucket(particle_speed, -1, 20, 105) ps
from particles)
select d diam_from, d+.2 diam_to, s speed_from, s+.2 speed_to, coalesce(t.cnt, 0) cnt
from (select generate_series(0, 9.8, 0.2)) as dm(d)
cross join (select generate_series(-1, 19.8, 0.2)) AS sp(s)
left join (
select pd * .2 - .2 diam_from, ps * .2 - 1.2 speed_from, count(1) cnt
from b group by pd, ps) t
on dm.d = t.diam_from and sp.s = t.speed_from