From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Dmitri Bichko" <dbichko(at)aveopharma(dot)com> |
Cc: | "Sql-Postgre" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Indices and user defined operators |
Date: | 2005-06-09 05:27:04 |
Message-ID: | 7273.1118294824@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I wrote:
> "Dmitri Bichko" <dbichko(at)aveopharma(dot)com> writes:
>> So, is there any way to make these operators use an index defined as
>> above?
> If you've set things up so that the operators are defined by inline-able
> SQL functions, I'd sort of expect it to fall out for free ...
Here's a quick proof-of-concept:
regression=# create function iequal(text,text) returns bool as
regression-# 'select upper($1) = upper($2)' language sql strict immutable;
CREATE FUNCTION
regression=# create operator *= (procedure = iequal, leftarg = text,
regression(# rightarg = text , commutator = *= );
CREATE OPERATOR
regression=# explain select * from text_tbl where f1 *= 'foo';
QUERY PLAN
---------------------------------------------------------
Seq Scan on text_tbl (cost=0.00..1.03 rows=1 width=32)
Filter: (upper(f1) = 'FOO'::text)
(2 rows)
regression=# create index fooi on text_tbl(upper(f1));
CREATE INDEX
regression=# set enable_seqscan TO 0; -- because my test table is tiny
SET
regression=# explain select * from text_tbl where f1 *= 'foo';
QUERY PLAN
----------------------------------------------------------------------
Index Scan using fooi on text_tbl (cost=0.00..4.68 rows=1 width=32)
Index Cond: (upper(f1) = 'FOO'::text)
(2 rows)
This is with CVS tip, but I'm pretty sure it works as far back as 7.4.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2005-06-09 06:56:54 | Re: SELECT very slow |
Previous Message | Tom Lane | 2005-06-09 04:01:56 | Re: Indices and user defined operators |