Re: Finding nearest numeric value

Yep, you're right. The following works and uses the index on pk_col:

prepare test_01 (bigint) as
select * from
(
(SELECT * FROM big_table WHERE pk_col > $1 ORDER BY pk_col LIMIT 1)
UNION ALL
(SELECT * FROM big_table WHERE pk_col < $1 ORDER BY pk_col DESC
LIMIT 1)
) as nearest
order by abs(pk_col - $1)
limit 1;

db=> explain execute test_01(12321);

QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.12..2.12 rows=1 width=112)
-> Sort (cost=2.12..2.13 rows=2 width=112)
Sort Key: abs((pk_col - $1))
-> Subquery Scan nearest (cost=0.00..2.11 rows=2 width=112)
-> Append (cost=0.00..2.08 rows=2 width=59)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.04
rows=1 width=59)
-> Limit (cost=0.00..1.03 rows=1 width=59)
-> Index Scan using idx_pk_col on
big_table (cost=0.00..36639172.72 rows=35532914 width=59)
Index Cond: (pk_col > $1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.04
rows=1 width=59)
-> Limit (cost=0.00..1.03 rows=1 width=59)
-> Index Scan Backward using
idx_pk_col on big_table (cost=0.00..36639172.72 rows=35532914 width=59)
Index Cond: (pk_col < $1)
(13 rows)

Cheers,
Csaba.

On Wed, 2005-08-17 at 17:57, Bruno Wolff III wrote:
> On Wed, Aug 17, 2005 at 17:35:37 +0200,
> Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> > The only problem is that you can't use the order by/limit syntax inside
> > the union queries I guess, cause the query you proposed is giving a
> > syntax error. I also thought first to do it like this, but it won't
> > work. If it would, then you could wrap the thing in another query which
> > orders by the difference and limits to the first one ;-)
>
> You probably can just add parenthesis. I think that the second ORDER BY
> and LIMIT may be being applied to the UNION results which would be a
> problem. Putting the second subquery in parens will take care of this if
> that is the problem.
>
> >
> > On Wed, 2005-08-17 at 17:10, Richard Huxton wrote:
> > > Poul Møller Hansen wrote:
> > > > Does anyone know how to find the row with the nearest numeric value, not
> > > > necessarily an exact match ?
> > >
> > > While the other answers all do their job, and in one go too, I'd be
> > > surprised if you found anything faster than:
> > >
> > > SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1
> > > UNION ALL
> > > SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1
> > >
> > > That gives you (up to) two values to look at, but should use any index
> > > you have on myval.
> > >
> > > You can always sort the results by abs(myval) then if you don't want to
> > > handle two values in the application layer.