When SELECTing an expression that is an expression based index does the expression get processed? I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
SELECT (first_name || ' ' || last_name) FROM people;
No, not currently for that example, for a few reasons.

First, the planner doesn't even entertain indexes unless they seem potentially useful to a WHERE or JOIN condition.  (This is to reduce planning time.) Since your example doesn't have either of those, it doesn't even consider the index.  You could fix this by adding where (first_name || ' ' || last_name) is not null to your query.

Second, the planner thinks it needs the inputs to the expression (even though it doesn't), and unless the inputs are present it won't use the index for an index-only scan.  You could fix this by adding the expression inputs to the index ON people ((first_name || ' ' || last_name),first_name, last_name) but that is certainly an unattractive work-around.

Finally, it might not realize that it can avoid recomputing the expression if the query is complex (although that isn't a problem for your example):


  An additional caveat, if the goal is to avoid recalculating f(x), is
  that the planner won't necessarily match uses of f(x) that aren't in
  indexable WHERE clauses to the index column. It will usually get this
  right in simple queries such as shown above, but not in queries that
  involve joins. These deficiencies may be remedied in future versions
  of PostgreSQL.