pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctional Indexes
13+ messages / 9 participants
[nested] [flat]
* Functional Indexes
@ 1999-02-05 22:54 Bryan White <bryan@arcamax.com>
0 siblings, 0 replies; 13+ messages in thread
From: Bryan White @ 1999-02-05 22:54 UTC (permalink / raw)
To: pgsql-sql
The documentation for CREATE INDEX implies that functions are allowed in
index definitions but when I execute:
create unique index idxtest on customer (lower(email));
the result is:
ERROR: DefineIndex: (null) class not found
Should this work? Do I have the syntax wrong?
Bryan White
ArcaMax Inc.
Yorktown VA
www.arcamax.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
@ 1999-02-06 17:27 Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Re: [SQL] Functional Indexes Sascha Schumann <sas@schell.de>
1999-03-15 14:53 ` Re: [HACKERS] Re: [SQL] Functional Indexes Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 1999-02-06 17:27 UTC (permalink / raw)
To: Bryan White <bryan@arcamax.com>; +Cc: pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
"Bryan White" <bryan@arcamax.com> writes:
> The documentation for CREATE INDEX implies that functions are allowed in
> index definitions but when I execute:
> create unique index idxtest on customer (lower(email));
> the result is:
> ERROR: DefineIndex: (null) class not found
> Should this work? Do I have the syntax wrong?
I tried this wih 6.4.2 and found that it was only accepted if I
explicitly identified which index operator class to use:
play=> create table customer (email text);
CREATE
play=> create unique index idxtest on customer (lower(email));
ERROR: DefineIndex: class not found
play=> create unique index idxtest on customer (lower(email) text_ops);
CREATE
play=>
That'll do as a workaround for Bryan, but isn't this a bug? Surely
the system ought to know what type the result of lower() is...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-02-08 00:28 ` Sascha Schumann <sas@schell.de>
1999-02-08 02:42 ` Re: [SQL] Functional Indexes Marc Howard Zuckman <marc@fallon.classyad.com>
1 sibling, 1 reply; 13+ messages in thread
From: Sascha Schumann @ 1999-02-08 00:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bryan White <bryan@arcamax.com>; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote:
> "Bryan White" <bryan@arcamax.com> writes:
> > The documentation for CREATE INDEX implies that functions are allowed in
> > index definitions but when I execute:
> > create unique index idxtest on customer (lower(email));
> > the result is:
> > ERROR: DefineIndex: (null) class not found
> > Should this work? Do I have the syntax wrong?
>
> I tried this wih 6.4.2 and found that it was only accepted if I
> explicitly identified which index operator class to use:
>
> play=> create table customer (email text);
> CREATE
> play=> create unique index idxtest on customer (lower(email));
> ERROR: DefineIndex: class not found
> play=> create unique index idxtest on customer (lower(email) text_ops);
> CREATE
> play=>
>
> That'll do as a workaround for Bryan, but isn't this a bug? Surely
> the system ought to know what type the result of lower() is...
>
> regards, tom lane
I still have a problem with that ... edited typescript follows
funweb=> \d userdat
Table = userdat
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| username | varchar() not null | 30 |
...
+----------------------------------+----------------------------------+-------+
Index: userdat_pkey
funweb=> create unique index userdat_idx2 on userdat (lower(username)
varchar_ops);
ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist
This error message looks very bogus to me.
--
Regards,
Sascha Schumann |
Consultant | finger sas@schell.de
| for PGP public key
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Re: [SQL] Functional Indexes Sascha Schumann <sas@schell.de>
@ 1999-02-08 02:42 ` Marc Howard Zuckman <marc@fallon.classyad.com>
1999-02-08 03:18 ` Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Marc Howard Zuckman @ 1999-02-08 02:42 UTC (permalink / raw)
To: Sascha Schumann <sas@schell.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bryan White <bryan@arcamax.com>; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
On Mon, 8 Feb 1999, Sascha Schumann wrote:
> On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote:
> > "Bryan White" <bryan@arcamax.com> writes:
> > > The documentation for CREATE INDEX implies that functions are allowed in
> > > index definitions but when I execute:
> > > create unique index idxtest on customer (lower(email));
> > > the result is:
> > > ERROR: DefineIndex: (null) class not found
> > > Should this work? Do I have the syntax wrong?
> >
> > I tried this wih 6.4.2 and found that it was only accepted if I
> > explicitly identified which index operator class to use:
> >
> > play=> create table customer (email text);
> > CREATE
> > play=> create unique index idxtest on customer (lower(email));
> > ERROR: DefineIndex: class not found
> > play=> create unique index idxtest on customer (lower(email) text_ops);
> > CREATE
> > play=>
> >
> > That'll do as a workaround for Bryan, but isn't this a bug? Surely
> > the system ought to know what type the result of lower() is...
> >
> > regards, tom lane
>
> I still have a problem with that ... edited typescript follows
>
> funweb=> \d userdat
> Table = userdat
> +----------------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +----------------------------------+----------------------------------+-------+
> | username | varchar() not null | 30 |
> ...
> +----------------------------------+----------------------------------+-------+
> Index: userdat_pkey
> funweb=> create unique index userdat_idx2 on userdat (lower(username)
> varchar_ops);
> ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>
> This error message looks very bogus to me.
>
> --
>
> Regards,
>
> Sascha Schumann |
> Consultant | finger sas@schell.de
> | for PGP public key
>
I don't think lower is defined for varchar arguments. consider redefining
username as type text and using text_ops.
This method worked on my system:
stocks=> create table temptext (a text, b varchar(20));
CREATE
stocks=> create index itemptext on temptext using btree(lower(a) text_ops) ;
CREATE
Your error reproduced:
stocks=> create index i2temptext on temptext using btree(lower(b) text_ops) ;
ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist
Excerpt from function definitions( both return value and argument are text
types):
text |lower |text |lowercase
Marc Zuckman
marc@fallon.classyad.com
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_ Visit The Home and Condo MarketPlace _
_ http://www.ClassyAd.com _
_ _
_ FREE basic property listings/advertisements and searches. _
_ _
_ Try our premium, yet inexpensive services for a real _
_ selling or buying edge! _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Re: [SQL] Functional Indexes Sascha Schumann <sas@schell.de>
1999-02-08 02:42 ` Re: [SQL] Functional Indexes Marc Howard Zuckman <marc@fallon.classyad.com>
@ 1999-02-08 03:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 03:48 ` Re: [SQL] Functional Indexes Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-08 15:56 ` Re: [HACKERS] Re: [SQL] Functional Indexes Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 1999-02-08 03:18 UTC (permalink / raw)
To: Marc Howard Zuckman <marc@fallon.classyad.com>; +Cc: pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
Marc Howard Zuckman <marc@fallon.classyad.com> writes:
> On Mon, 8 Feb 1999, Sascha Schumann wrote:
>> funweb=> create unique index userdat_idx2 on userdat (lower(username)
>> varchar_ops);
>> ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>>
>> This error message looks very bogus to me.
> I don't think lower is defined for varchar arguments. consider redefining
> username as type text and using text_ops.
I think Marc is right. Someone was working on adding lower() to the
available ops for varchar for 6.5, but it's not there in 6.4.
You can get lower() to work on varchar source data in a simple
SELECT, but that's some sort of hack that involves the system
knowing that text and varchar have the same physical representation
so it's OK to use a function that takes text on a varchar column.
The type matching requirements for functional indexes are tighter.
Note to hackers: is there a good reason why indexes are more
restrictive? Offhand it seems like the same physical-equivalence
trick could be applied.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Re: [SQL] Functional Indexes Sascha Schumann <sas@schell.de>
1999-02-08 02:42 ` Re: [SQL] Functional Indexes Marc Howard Zuckman <marc@fallon.classyad.com>
1999-02-08 03:18 ` Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-02-08 03:48 ` Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 1999-02-08 03:48 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: marc@fallon.classyad.com; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
>
> Note to hackers: is there a good reason why indexes are more
> restrictive? Offhand it seems like the same physical-equivalence
> trick could be applied.
What do you mean by restrictive. If you mean:
* allow creation of functional indexes to use default types
It is on the TODO list, and has been for a while.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [HACKERS] Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Re: [SQL] Functional Indexes Sascha Schumann <sas@schell.de>
1999-02-08 02:42 ` Re: [SQL] Functional Indexes Marc Howard Zuckman <marc@fallon.classyad.com>
1999-02-08 03:18 ` Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-02-08 15:56 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 13+ messages in thread
From: Thomas G. Lockhart @ 1999-02-08 15:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Howard Zuckman <marc@fallon.classyad.com>; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
> > I don't think lower is defined for varchar arguments.
> Note to hackers: is there a good reason why indexes are more
> restrictive? Offhand it seems like the same physical-equivalence
> trick could be applied.
Well, we should have a combination of "binary compatible" and type
coersion to make this fly, just as we have in other places for v6.4. I
didn't realize this index code was there, so never looked at it.
If someone else doesn't get to it, I'll try to look at it before or
during 6.5beta...
- Tom
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [HACKERS] Re: [SQL] Functional Indexes
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-03-15 14:53 ` Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 1999-03-15 14:53 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: bryan@arcamax.com; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org
On the TODO list:
* allow creation of functional indexes to use default types
> "Bryan White" <bryan@arcamax.com> writes:
> > The documentation for CREATE INDEX implies that functions are allowed in
> > index definitions but when I execute:
> > create unique index idxtest on customer (lower(email));
> > the result is:
> > ERROR: DefineIndex: (null) class not found
> > Should this work? Do I have the syntax wrong?
>
> I tried this wih 6.4.2 and found that it was only accepted if I
> explicitly identified which index operator class to use:
>
> play=> create table customer (email text);
> CREATE
> play=> create unique index idxtest on customer (lower(email));
> ERROR: DefineIndex: class not found
> play=> create unique index idxtest on customer (lower(email) text_ops);
> CREATE
> play=>
>
> That'll do as a workaround for Bryan, but isn't this a bug? Surely
> the system ought to know what type the result of lower() is...
>
> regards, tom lane
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Functional Indexes
@ 1999-02-08 18:36 Bryan White <bryan@arcamax.com>
0 siblings, 0 replies; 13+ messages in thread
From: Bryan White @ 1999-02-08 18:36 UTC (permalink / raw)
To: pgsql-sql
OK, I have used the correct incantation to create the index and that works.
I don't see how to use this index in a select. I guess I need an operator
that is case-insensitive but does not use regular expressions. There does
not seem to be one listed in the Manual.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Functional Indexes
@ 2003-07-15 17:36 David Olbersen <DOlbersen@stbernard.com>
2003-07-15 18:12 ` Re: Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 18:30 ` Re: Functional Indexes Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 13+ messages in thread
From: David Olbersen @ 2003-07-15 17:36 UTC (permalink / raw)
To: pgsql-sql
Hello all,
I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com".
I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK).
urltld uses urlhost to do it's job (how should be apparent).
Now the question: is there a single index I can create that will be used when my WHERE clause contains either urlhost or urltld? I could create two functional indexes, but that seems a bit silly to me.
Any thoughts?
--------------------------
David Olbersen
iGuard Engineer
St. Bernard Software
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functional Indexes
2003-07-15 17:36 Functional Indexes David Olbersen <DOlbersen@stbernard.com>
@ 2003-07-15 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2003-07-15 18:12 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-sql
"David Olbersen" <DOlbersen@stbernard.com> writes:
> I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com".
> I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK).
> urltld uses urlhost to do it's job (how should be apparent).
> Now the question: is there a single index I can create that will be
> used when my WHERE clause contains either urlhost or urltld?
I do not see any way with functions declared like that. Quite aside
from implementation limitations, the portion of the 'host' string that
urltld is interested in would be the low-order part of the indexed
strings, and you can't usefully use an index to search for low-order
digits of the key.
Could you instead define an index over the reversed host name (eg,
com.foobar.www)? This would seem to provide about the same
functionality for searches on urlhost, and you could exploit the index
for TLD searching via prefixes. For example:
regression=# create table t1 (f1 text);
CREATE TABLE
regression=# create index t1i on t1 (lower(f1));
CREATE INDEX
regression=# explain select * from t1 where lower(f1) like 'com.%';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using t1i on t1 (cost=0.00..17.08 rows=5 width=32)
Index Cond: ((lower(f1) >= 'com.'::text) AND (lower(f1) < 'com/'::text))
Filter: (lower(f1) ~~ 'com.%'::text)
(3 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functional Indexes
2003-07-15 17:36 Functional Indexes David Olbersen <DOlbersen@stbernard.com>
@ 2003-07-15 18:30 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 13+ messages in thread
From: Joe Conway @ 2003-07-15 18:30 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-sql
David Olbersen wrote:
> Now the question: is there a single index I can create that will be
> used when my WHERE clause contains either urlhost or urltld? I could
> create two functional indexes, but that seems a bit silly to me.
>
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW, this works in 7.4devel (which should be in beta next Monday):
create or replace function tld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',3)
' language 'sql' STRICT IMMUTABLE;
regression=# select tld('http://www.foobar.com/really/long/path/to/a/file';);
tld
-----
com
(1 row)
create or replace function sld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',2)
' language 'sql' STRICT IMMUTABLE;
regression=# select sld('http://www.foobar.com/really/long/path/to/a/file';);
sld
--------
foobar
(1 row)
create table urls(f1 text);
insert into urls values('http://www.foobar.com/really/long/path/to/a/file';);
create index urls_idx1 on urls(tld(f1),sld(f1));
-- just to see index usage on toy table
set enable_seqscan to off;
regression=# explain analyze select * from urls where tld(f1) = 'com';
QUERY PLAN
----------------------------------------------------------------------
Index Scan using urls_idx1 on urls (cost=0.00..4.69 rows=1 width=32)
(actual time=0.07..0.07 rows=1 loops=1)
Index Cond: (split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text)
Total runtime: 0.18 msec
(3 rows)
regression=# explain analyze select * from urls where tld(f1) = 'com'
and sld(f1) = 'foobar';
QUERY PLAN
---------------------------------------------------------------------
Index Scan using urls_idx1 on urls (cost=0.00..4.70 rows=1 width=32)
(actual time=0.08..0.09 rows=1 loops=1)
Index Cond: ((split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text) AND
(split_part(split_part(substr(f1, 8, length(f1)), '/'::text, 1),
'.'::text, 2) = 'foobar'::text))
Total runtime: 0.21 msec
(3 rows)
Joe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functional Indexes
@ 2003-07-15 18:06 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 13+ messages in thread
From: Frank Bax @ 2003-07-15 18:06 UTC (permalink / raw)
To: pgsql-sql
The two functions do not group data the same way, so a common index is not
possible.
urlhost would put pgsql.org and pgsql.com close together.
urltld would but pgsql.com and xyz.com close together.
Frank
At 01:36 PM 7/15/03, David Olbersen wrote:
>Hello all,
>
>I have a function (urlhost) which finds the 'host' portion of a URL. In
>the case of http://www.foobar.com/really/long/path/to/a/file it returns
>"www.foobar.com".
>
>I also have a function (urltld) which returns the TLD of a URL. In the
>case of http://www.foobar.com/really/long/path/to/a/file it returns ".com"
>(the leading dot is OK).
>
>urltld uses urlhost to do it's job (how should be apparent).
>
>Now the question: is there a single index I can create that will be used
>when my WHERE clause contains either urlhost or urltld? I could create
>two functional indexes, but that seems a bit silly to me.
>
>Any thoughts?
>
>--------------------------
>David Olbersen
>iGuard Engineer
>St. Bernard Software
>11415 West Bernardo Court
>San Diego, CA 92127
>1-858-676-2277 x2152
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-07-15 18:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-05 22:54 Functional Indexes Bryan White <bryan@arcamax.com>
1999-02-06 17:27 Re: [SQL] Functional Indexes Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 00:28 ` Sascha Schumann <sas@schell.de>
1999-02-08 02:42 ` Marc Howard Zuckman <marc@fallon.classyad.com>
1999-02-08 03:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-02-08 03:48 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-08 15:56 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-03-15 14:53 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-08 18:36 Re: [SQL] Functional Indexes Bryan White <bryan@arcamax.com>
2003-07-15 17:36 Functional Indexes David Olbersen <DOlbersen@stbernard.com>
2003-07-15 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 18:30 ` Joe Conway <mail@joeconway.com>
2003-07-15 18:06 Re: Functional Indexes Frank Bax <fbax@sympatico.ca>
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