functional indexes and their costs

From: Adam <ahansen(at)lyrical(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: functional indexes and their costs
Date: 2003-03-13 21:36:42
Message-ID: 1047591402.26998.27.camel@ahansen.ofsloans.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

refer to the following 2 querys which were run after a vacuum analyze...

=# 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Petre Scheie 2003-03-13 21:42:19 Re: PL/Java (was: stored procedures)
Previous Message Bruno Wolff III 2003-03-13 21:35:01 Re: unlock rows