Is there a postgres CLOSEST operator?

by

I may be a little off on the syntax, but this parameterized query (all the ? take the ‘1’ of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed].

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;

The query plan for this with a table of ~5e5 rows (with an index on number) looks like this:

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)

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. Using an Alias column in the where clause in Postgresql
  3. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  4. Possible to perform cross-database queries with PostgreSQL?
  5. Reset auto increment counter in postgres
  6. Optimize query with OFFSET on large table
  7. GROUP BY and aggregate sequential numeric values
  8. Safely rename tables using serial primary key columns
  9. How to concatenate columns in a Postgres SELECT?
  10. How to use (install) dblink in PostgreSQL?
  11. updating table rows in postgres using subquery
  12. Find overlapping date ranges in PostgreSQL
  13. PostgreSQL multi INSERT…RETURNING with multiple columns
  14. How do you do date math that ignores the year?
  15. Postgres manually alter sequence
  16. What’s the proper index for querying structures in arrays in Postgres jsonb?
  17. “Operator does not exist: integer =?” when using Postgres
  18. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  19. Oracle equivalent of Postgres’ DISTINCT ON?
  20. postgresql generate sequence with no gap
  21. PostgreSQL: Case insensitive string comparison
  22. Combine two columns and add into one new column
  23. Bulk/batch update/upsert in PostgreSQL
  24. Grant privileges for a particular database in PostgreSQL
  25. Functions with variable number of input parameters
  26. Timestamp Difference In Hours for PostgreSQL
  27. SQL INSERT without specifying columns. What happens?
  28. Run a query with a LIMIT/OFFSET and also get the total number of rows
  29. How to create a new database with the hstore extension already installed?
  30. Padding zeros to the left in postgreSQL

Leave a Comment