On 22.03.2013 02:05, Josh Berkus wrote:
Well, no.<@ is not a btree-indexable operator.
Yes, but it's equivalent to ( ( a>= b1 or b1 is null ) and ( a< b2 or
b2 is null ) ), which *is* btree-indexable and can use an index. So it
seems like the kind of optimization we could eventually make.
Yeah. The sort order of <@ is the same as regular b-tree, so it should
be possible. In fact, nothing stops you from creating the suitable
operator and b-tree support functions. See attached patch for int4, but
the same should work for timestamptz.
We should do this automatically. Or am I missing something?
- Heikki
CREATE or replace FUNCTION btint4rangecmp(int4, int4range) RETURNS int4 AS
$$
SELECT CASE WHEN $1 < lower($2) THEN -1
WHEN $1 < upper($2) OR upper($2) IS NULL THEN 0
ELSE 1 END
$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE FUNCTION int4_contained_by_int4range (int4, int4range) RETURNS bool AS
'elem_contained_by_range' LANGUAGE internal IMMUTABLE STRICT;
CREATE OPERATOR <@ (
PROCEDURE=int4_contained_by_int4range,
LEFTARG=int4, RIGHTARG=int4range
);
ALTER OPERATOR FAMILY integer_ops USING btree ADD
OPERATOR 3 <@ (int4, int4range),
FUNCTION 1 btint4rangecmp(int4, int4range);
-- Create a test table
create table inttable (i int4 primary key);
insert into inttable select g from generate_series(1,100000) g;
-- Query it. Uses index, hooray!
select * from inttable where i <@ int4range(111, 120);
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
