我正在寻找一些东西,给一张桌子像：
| id | number | | 1 | .7 | | 2 | 1.25 | | 3 | 1.01 | | 4 | 3.0 |
查询SELECT * FROM my_table WHEREnumberCLOSEST(1)将返回第3行.我只关心数字.现在我有一个程序,只是循环遍历每一行,并进行比较,但我认为信息应该可以从b树索引,所以这可能是一个内置的,但我找不到任何提示它的文件.
解决方法
我可能会稍微偏离语法,但是这个参数化查询(所有这些都是原始问题的’1′)应该运行得很快,基本上是2个B树查找[假设number被索引].
SELECT * FROM ( (SELECT id,number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above UNION ALL (SELECT id,number FROM t WHERE number < ? ORDER BY number DESC LIMIT 1) as below ) ORDER BY abs(?-number) LIMIT 1;
具有〜5e5行(数字索引)的表的查询计划如下所示：
psql => explain select * from ( (SELECT id,number FROM t WHERE number >= 1 order by number limit 1) union all (select id,number from t where number < 1 order by number desc limit 1) ) as make_postgresql_happy order by abs (1 - number) limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=0.24..0.24 rows=1 width=12) -> Sort (cost=0.24..0.24 rows=2 width=12) Sort Key: (abs((1::double precision - public.t.number))) -> Result (cost=0.00..0.23 rows=2 width=12) -> Append (cost=0.00..0.22 rows=2 width=12) -> Limit (cost=0.00..0.06 rows=1 width=12) -> Index Scan using idx_t on t (cost=0.00..15046.74 rows=255683 width=12) Index Cond: (number >= 1::double precision) -> Limit (cost=0.00..0.14 rows=1 width=12) -> Index Scan Backward using idx_t on t (cost=0.00..9053.67 rows=66136 width=12) Index Cond: (number < 1::double precision) (11 rows)
