Message-ID: <514CE0DA.5040109@vmware.com>
Date: Sat, 23 Mar 2013 00:53:14 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Jeff Davis <pgsql@j-davis.com>
Subject: Re: Index usage for tstzrange?
References: <514A5088.6060101@agliodbs.com> <1326.1363838330@sss.pgh.pa.us>
	<514BA04B.3040609@agliodbs.com>
In-Reply-To: <514BA04B.3040609@agliodbs.com>
Content-Type: multipart/mixed; boundary="------------030006030907050402010107"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------030006030907050402010107
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------030006030907050402010107
Content-Type: text/x-sql;
	name="btree-support-for-int4-contained-by-int4range.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="btree-support-for-int4-contained-by-int4range.sql"

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);

--------------030006030907050402010107
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--------------030006030907050402010107--