By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

functional index "real world" uses

P: n/a
I want to know how functional indexes are used "in the real world". Here
are the common uses:

* non-unique index on the first parts of a longish text field
(SUBSTRING(field)) to save disk space, while still allowing faster
searches than a sequential scan.

* indexing on LOWER(field)/UPPER(field) to allow case-insensitive
searches or case-insensitive unique constraint.

In what other scenario are you using functional index? Searching the
archives, I found someone indexing a date as 'YYYYMMDD', probably to
allow searching using LIKE 'YYYY%' or LIKE 'YYYYMM%'. I've also thought:

* creating a REVERSE_DOT(field) function for IP addresses and hostnames
to allow wildcard searches (*.yahoo.com) using 'REVERSE_DOT(f) LIKE
'com.yahoo.%'.

* creating a HOSTPART(field) and LOCALPART(field) for email addressess;

Has anyone done something like this or other tricks using functional
indexes? It seems potential to simplify data model (no need to store
email addresses as host + part, etc).

--
dave

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, Feb 06, 2004 at 12:29:07AM +0700, David Garamond wrote:
* creating a REVERSE_DOT(field) function for IP addresses and hostnames
to allow wildcard searches (*.yahoo.com) using 'REVERSE_DOT(f) LIKE
'com.yahoo.%'.


I know a local telco company (hum, is this RAS syndrome?) uses this,
reversing phone numbers, so they allow search by prefix and suffix.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.