http://www.perlmonks.org?node_id=512605


in reply to (OT) Don't blow that index dude.

PostgreSQL has a feature where you can index based on an expression. Details here.

thor

The only easy day was yesterday

Replies are listed 'Best First'.
Re^2: (OT) Don't blow that index dude.
by jplindstrom (Monsignor) on Nov 29, 2005 at 14:32 UTC
    And, for reference, so does Oracle.

    /J

Re^2: (OT) Don't blow that index dude.
by Errto (Vicar) on Nov 29, 2005 at 19:52 UTC
    True, but here (and in Oracle and other DBs with this feature), it's not magical. That is, you have to decide ahead of time exactly what expression you want to index on and then make your WHERE conditions query on exactly that expression. That said, especially for simple things like case-insensitive comparison (by creating an index on LOWER(somecol) ) this feature can offer an enormous performance benefit in many cases.
      That's the same for /any/ index. The difference between a functional and regular index, is how you specify it. If you create an index on a column, you're creating it on a column. If you are doing it on f(column), you're creating it on f(column).

      You always have to be specific. Can't ask for one thing and expect another. :)

      ----
      Give me strength for today.. I will not talk it away..
      Just for a moment.. It will burn through the clouds.. and shine down on me.