Query column with range types

Rationale

The main use case for range types is to store ranges in PostgreSQL tables, and then find rows whose range includes a certain literal. These can already be indexed using GIN and GiST index types.

db=# EXPLAIN ANALYZE SELECT * FROM rng WHERE rng @> 10.0;
Bitmap Heap Scan on rng  (cost=48.40..2709.45 rows=1000 width=32) (actual time=0.103..0.103 rows=10 loops=1)
  Recheck Cond: (rng @> 10.0)
  ->  Bitmap Index Scan on rng_rng_idx  (cost=0.00..48.15 rows=1000 width=0) (actual time=0.096..0.096 rows=10 loops=1)
        Index Cond: (rng @> 10.0)
Total runtime: 0.136 ms

However, you cannot index inverse queries - if you want to find single values in a certain range:

db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[0, 10)';
Seq Scan on num  (cost=0.00..16925.00 rows=1000 width=6) (actual time=0.014..188.761 rows=9 loops=1)
  Filter: (i <@ '[0,10]'::numrange)
  Rows Removed by Filter: 999991
Total runtime: 188.820 ms

Example

Of course the above query is equivalent to the indexable query WHERE num >= 0 AND num < 10. But what if you want to use range types? Well, you can create a custom operator with a function that automatically gets inlined:

db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[0, 10)';
Index Only Scan using num_i_idx on num  (cost=0.00..4.56 rows=10 width=6) (actual time=0.014..0.016 rows=9 loops=1)
  Index Cond: ((i >= 0::numeric) AND (i < 10::numeric))
  Heap Fetches: 0
Total runtime: 0.039 ms

Infinite terms get optimized out:

db=# EXPLAIN ANALYZE SELECT * FROM num WHERE i <@ '[,10]';
Index Only Scan using num_i_idx on num  (cost=0.00..4.53 rows=10 width=6) (actual time=0.007..0.009 rows=10 loops=1)
  Index Cond: (i <= 10::numeric)
  Heap Fetches: 0
Total runtime: 0.032 ms

Beware!

These custom operators are deliberately defined to be non-commative. If you create these operators, do not write range queries in the form 10.0 <@ range_col because it will blow up:

db=# EXPLAIN ANALYZE SELECT * FROM rng WHERE 10.0 <@ rng;
Seq Scan on rng  (cost=0.00..46358.16 rows=444424 width=32) (actual time=0.013..240.054 rows=10 loops=1)
  Filter: ((lower_inf(rng) OR CASE WHEN lower_inc(rng) THEN (10.0 >= lower(rng)) ELSE (10.0 > lower(rng)) END)
       AND (upper_inf(rng) OR CASE WHEN upper_inc(rng) THEN (10.0 <= upper(rng)) ELSE (10.0 < upper(rng)) END))
  Rows Removed by Filter: 999990
Total runtime: 240.125 ms

Code

You can adapt this code to any range type you need, just by changing the types of the function and the operator.

-- int4range (integer)
CREATE OR REPLACE FUNCTION range_contains(el integer, rng int4range) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=integer, rightarg=int4range);

-- int8range (bigint)
CREATE OR REPLACE FUNCTION range_contains(el bigint, rng int8range) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=bigint, rightarg=int8range);

-- numrange (numeric)
CREATE OR REPLACE FUNCTION range_contains(el numeric, rng numrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=numeric, rightarg=numrange);

-- tsrange (timestamp without time zone)
CREATE OR REPLACE FUNCTION range_contains(el timestamp, rng tsrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=timestamp, rightarg=tsrange);

-- tstzrange (timestamp with time zone)
CREATE OR REPLACE FUNCTION range_contains(el timestamptz, rng tstzrange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=timestamptz, rightarg=tstzrange);

-- daterange (date)
CREATE OR REPLACE FUNCTION range_contains(el date, rng daterange) RETURNS bool IMMUTABLE LANGUAGE sql AS $$
SELECT (lower_inf(rng) OR (CASE WHEN lower_inc(rng) THEN el >= lower(rng) ELSE el > lower(rng) END))
   AND (upper_inf(rng) OR (CASE WHEN upper_inc(rng) THEN el <= upper(rng) ELSE el < upper(rng) END))
$$;
CREATE OPERATOR <@ (procedure=range_contains, leftarg=date, rightarg=daterange);

Idea from Andres Freund, implemented by Marti Raudsepp.