From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | functional index "real world" uses |
Date: | 2004-02-05 17:29:07 |
Message-ID: | 40227D63.8050405@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Ojea Castro | 2004-02-05 17:31:06 | Re: Proper tool to display graphics? |
Previous Message | Joe Conway | 2004-02-05 17:27:37 | Re: dblink: rollback transaction |