Thread: BUG #5000: Optimizer does not use function-based index for an order by
BUG #5000: Optimizer does not use function-based index for an order by
From
"Brian Ceccarelli"
Date:
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: =A0 =A0 =A0 =A0Optimizer does not use function-based index f= or 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. --=20 greg http://mit.edu/~gsstark/resume.pdf