Re: Finding nearest numeric value

[snip]
> To find the nearest value in number_column to some CONSTANT (where you
> replace constant with a number), try:
>
> select *,(number_column - CONSTANT)^2 as d from tablename order by d limit
> 1;
>
This will scan the whole table and sort the results... and then pick
just one of it. Watch this:

db=> prepare test_01(bigint) as select *, (pk_col - $1) ^ 2 as d from
big_table order by d limit 1;
PREPARE
eb=> explain execute test_01(27163619);
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=31239164.71..31239164.72 rows=1 width=59)
-> Sort (cost=31239164.71..31505657.00 rows=106596914 width=59)
Sort Key: (((pk_col - $1))::double precision ^ 2::double
precision)
-> Seq Scan on big_table (cost=0.00..3149688.00
rows=106596914 width=59)
(4 rows)

The names were changed, this is a production DB, but the idea is:
big_table has around 100 million rows, and pk_col is the primary key on
it. Running the above query would take forever.

If you don't have an index on the numeric column, or if the table is
small, this might be your best choice... but if your table is big, and
you have an index on the numeric column, you should use something along:

select * number_col from big_table where number_col < CONSTANT order by
number_col desc limit 1

select * number_col from big_table where number_col > CONSTANT order by
number_col limit 1

You execute the 2 queries, which are very fast even for big tables if
you have an index on number_col, and then choose the row with the
smallest difference (you do this in your client program).

HTH,
Csaba.

> Does that do it for you?
>
> Sean
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq