我要找的是这样的东西,比如:
| id | number |
| 1 | .7 |
| 2 | 1.25 |
| 3 | 1.01 |
| 4 | 3.0 |
查询SELECT * FROM my_table WHERE
numberCLOSEST(1)
将返回第3行.我只在乎数字.现在,我有一个过程,它只是在每一行上循环并进行比较,但我认为信息应该可以从b树索引中获得,所以这可能是一个内置的,但我找不到任何文档表明它可以.
我要找的是这样的东西,比如:
| id | number |
| 1 | .7 |
| 2 | 1.25 |
| 3 | 1.01 |
| 4 | 3.0 |
查询SELECT * FROM my_table WHERE
numberCLOSEST(1)
将返回第3行.我只在乎数字.现在,我有一个过程,它只是在每一行上循环并进行比较,但我认为信息应该可以从b树索引中获得,所以这可能是一个内置的,但我找不到任何文档表明它可以.
我可能有点不懂语法,但这个参数化查询(以原始问题的"1"为例)应该运行得很快,基本上是2个B树查找(假设数字已被索引).
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行(索引为number
)的表,如下所示:
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)