pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndices and user defined operators
4+ messages / 3 participants
[nested] [flat]
* Indices and user defined operators
@ 2005-06-09 02:23 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dmitri Bichko @ 2005-06-09 02:23 UTC (permalink / raw)
To: pgsql-sql
Being lazy, I've created a set of case incensitive text comparison
operators: =*, <*, >*, and !=*; the function for each just does an
UPPER() on both arguments and then uses the corresponding builtin
operator.
What would make these REALLY useful, is if when running something like:
SELECT * FROM foo WHERE bar =* 'baz';
postgres would know to use an index defined as:
CREATE INDEX idx_foo_bar ON foo (UPPER(bar));
Currently, the explain I get for the above situation is:
Seq Scan on foo (cost=0.00..8696.81 rows=1324 width=34)
Filter: (upper(upper((bar)::text)) = 'BAZ'::text)
I am vaguely aware of the concept of operator classes, but from what I
understand, the op class has to be specified at index creation time;
seeing how this is just to save a little typing, I'd rather not have to
rely on these ops and opclasses being defined for the rest of the
database to work. Also I need to still be able to do SELECT * FROM foo
WHERE UPPER(bar) = UPPER('baz') and have it use the same index.
So, is there any way to make these operators use an index defined as
above?
Thanks,
Dmitri
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indices and user defined operators
@ 2005-06-09 04:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-06-09 04:01 UTC (permalink / raw)
To: Dmitri Bichko <dbichko@aveopharma.com>; +Cc: pgsql-sql
"Dmitri Bichko" <dbichko@aveopharma.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 ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indices and user defined operators
@ 2005-06-09 05:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-06-09 05:27 UTC (permalink / raw)
To: Dmitri Bichko <dbichko@aveopharma.com>; +Cc: pgsql-sql
I wrote:
> "Dmitri Bichko" <dbichko@aveopharma.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indices and user defined operators
@ 2005-06-09 07:27 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2005-06-09 07:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dmitri Bichko <dbichko@aveopharma.com>; pgsql-sql
Tom Lane wrote:
> I wrote:
>
>>"Dmitri Bichko" <dbichko@aveopharma.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 ...
Now *that* is very nice. Thanks for the example Tom - nice to know the
sql list is still teaching me things within the first 5 messages I read.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-09 07:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-09 02:23 Indices and user defined operators Dmitri Bichko <dbichko@aveopharma.com>
2005-06-09 04:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-09 05:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-09 07:27 ` Richard Huxton <dev@archonet.com>
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