How to create an index based on a function

It is often handy to create indexes that are based on calculations of a function. One reason is that instead of storing the calculated value in the table along with the actual value, you save a bit on table scan speed since your row is thinner and also saves some disk space. It helps search speed if its a common function search.

Case in point, PostgreSQL is case sensitive so in order to do a simple search you will often resort to using upper or ILIKE. In those cases its useful to have an index on upper or lower cased text. Here is an example.

CREATE INDEX mem_name_idx
  ON member
  USING btree
  (upper(last_name), upper(first_name));
  

Here is another example taken from PostGIS land. Often times you provide your data in various transformation, but for space savings and row seek reasons, you want to only transform your data to the less used projections as needed. One way to do this is to create functional indexes on the commonly used transformations and create views or just write raw SQL that uses these alternative transformations.

   CREATE INDEX parcels_idx_geom_nadm  
       ON parcels USING gist(ST_Transform(the_geom, 26986))

So now when I do a select like this that lists all buildings within 100 meters of my NAD 83 MA Meter State Plane point of interest:

    SELECT bldg_name, ST_Transform(the_geom,26986) As newgeom
        FROM buildings 
        WHERE ST_DWithin(ST_Transform(the_geom, 26986) ,ST_GeomFromText('POINT(235675.754215375 894022.495855985)', 26986),  100) 

it will use indexes