From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Jeff Davis <pgsql@j-davis.com>
Subject: Re: Index usage for tstzrange?
Date: Sat, 23 Mar 2013 00:53:14 +0200
Message-ID: <514CE0DA.5040109@vmware.com> (raw)
In-Reply-To: <514BA04B.3040609@agliodbs.com>
References: <514A5088.6060101@agliodbs.com>
<1326.1363838330@sss.pgh.pa.us>
<514BA04B.3040609@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performanceAttachments:btree-support-for-int4-contained-by-int4range.sql (text/x-sql, 830B) download
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, josh@agliodbs.com, tgl@sss.pgh.pa.us, pgsql@j-davis.com
Subject: Re: Index usage for tstzrange?
In-Reply-To: <514CE0DA.5040109@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox