Re: Finding nearest numeric value - Mailing list pgsql-general

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@ecircle-ag.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.