Re: functional indexes and their costs

Adam <ahansen(at)lyrical(dot)net> writes:
> =# EXPLAIN SELECT * FROM sales_personal WHERE last_name='hansen';
> NOTICE: QUERY PLAN:

> Index Scan using sales_personal_last_name_index on sales_personal
> (cost=0.00..280.68 rows=81 width=618)

> EXPLAIN
> =#EXPLAIN SELECT * FROM sales_personal WHERE lower(last_name)='hansen';
> NOTICE: QUERY PLAN:

> Index Scan using test_lower_idx on sales_personal (cost=0.00..5827.83
> rows=1642 width=618)

> EXPLAIN

> anyone have any idea why the cost of using the functional index is so
> much greater than the cost of using the regular (unfunctioned) index on
> the same column?

You're falling into the classic beginner's trap of assuming that
EXPLAIN's estimates are the same as reality ;-). Did you try EXPLAIN
ANALYZE?

The reason for the difference is that the second case has a much larger
estimate of the number of rows selected from the index. This is not
based on anything very meaningful, because Postgres doesn't currently
keep any statistics that would allow a realistic estimate of the number
of rows matching a functional-index query. I would like to think that
the "81" is a reasonably good estimate for the former query --- but the
"1642" is purely and simply a guess for the latter.

regards, tom lane