BUG #5000: Optimizer does not use function-based index for an order by
The following bug has been logged online:
Bug reference: 5000
Logged by: Brian Ceccarelli
Email address: ceccareb@talusmusic.com
PostgreSQL version: 8.2.13
Operating system: Linux
Description: Optimizer does not use function-based index for an order
by
Details:
Subject line says it all.
I have a function-based index on a large table.
select *
from net32_product_def
order by (f_squash_descr(descr::text))
create unique index net32_product_def_text_idx on net32_product_def (
f_squash_descr(descr) )
The explain plan says that Postgres always does a table scan instead of
using the index. The table is 500,000 rows long, and the function
f_squash_descr() is a CPU-instensive operation. What should come
instantly, takes 1 minute.
Description: Optimizer does not use function-based index for an order
by
Details:Subject line says it all.
The optimizer makes an educated guess about whether to use indexes or
not. Without more information it's impossible to second-guess it.
You might want to ask on pgsql-general for advice on whether it's
making a good choice and how to influence it. But you'll have to
provide more information such as the actual schema and explain plans
of with and without enable_sort or enable_seqscan disabled.
Also 8.2 came out two years ago, if you're just starting with Postgres
you might as well use 8.4 which has features which will make this
easier -- including the ability to tell the planner how expensive your
function is.