sql - 在多个输入上高效地运行 SQL 查询

Question

您好，我有一个模拟快照，当前作为表存储在 PostgreSQL 数据库中，快照表的模式是

simdb=> \d isonew_4.snapshot_102
Table "isonew_4.snapshot_102"
Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 x      | real    | 
 y      | real    | 
 z      | real    | 
 vx     | real    | 
 vy     | real    | 
 vz     | real    | 
 pot    | real    | 
 mass   | real    | 
Indexes:
    "snapshot_102_id_idx" btree (id) WITH (fillfactor=100)

我有一个查询计算单个半径罚款所包含的质量:

SELECT SUM(mass) AS mass
FROM isonew_4.snapshot_102 AS s
WHERE SQRT(s.x^2 + s.y^2 + s.z^2) < {radius}

但是我想在许多不同的半径上运行它。

由于该表有大约 1 亿行，所以我更愿意将其作为 SQL 查询来执行，而不是获取所有粒子并在 python 中使用类似 numpy.histogram 的东西来进行分箱在我本地的机器上。

Answer 1

方法#1

此查询可能有效，例如 10,20 和 25 作为半径的连续值:

WITH r(radius) as (values (10),(20),(25))
  SELECT radius, SUM(mass) AS mass
  FROM isonew_4.snapshot_102 AS s CROSS JOIN r
  WHERE SQRT(s.x^2 + s.y^2 + s.z^2) < radius
  GROUP BY radius;

输出有两列:radius 和对应的sum(mass)。

---

方法#2

如果由于与列表的 CROSS JOIN 而导致查询速度太慢(大概，EXPLAIN 或更好的 EXPLAIN ANALYZE 可以确定)，一种不同的方法肯定保证大表的单次扫描是将所有结果收集在一行中，每个半径一列，生成的查询如下所示:

SELECT 
 sum(case when r < 10 then s.mass else 0 end) as radius10,
 sum(case when r < 20 then s.mass else 0 end) as radius20,
 sum(case when r < 25 then s.mass else 0 end) as radius25
FROM (select mass,SQRT(x^2 + y^2 + z^2) as r from isonew_4.snapshot_102) AS s

---

方法#3

如果不切实际，另一种可能值得尝试的完全不同的方法是在 btree 中预先计算 SQRT(x^2 + y^2 + z^2) functional index 希望SQL引擎可以用它来做不等式比较。这种情况是否发生以及查询是否会更快主要取决于数据分布。

create index radius_idx on isonew_4.snapshot_102(SQRT(x^2 + y^2 + z^2));

然后使用第一个查询，或者每次重复一个半径，或者使用方法 #1 和 GROUP BY 并同时使用所有值。如果值非常有选择性，则执行速度可能比单个大型顺序扫描要快得多。