|
Home > Archive > PostgreSQL Bugs > December 2005 > BUG #2126: Index usage for function value
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BUG #2126: Index usage for function value
|
|
| Grzegorz TaĆczyk 2005-12-27, 9:23 am |
|
The following bug has been logged online:
Bug reference: 2126
Logged by: Grzegorz TaĆczyk
Email address: goliatus@polzone.pl
PostgreSQL version: 8.1
Operating system: Slackware
Description: Index usage for function value
Details:
SELECT * FROM table WHERE id = myfunction('x', 10);
There is an index created on id column, but query planner doesn't use it.
When I explain this query:
SELECT * FROM table WHERE id = (SELECT myfunction('x', 10))
Then index is used and execution is much faster.
Is this subquery nessesary?
It doesn't happen in all circumstances, but when query is more complex and
table is big then it happens.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2005-12-27, 11:23 am |
| "Grzegorz TaĆczyk" <goliatus@polzone.pl> writes:
> SELECT * FROM table WHERE id = myfunction('x', 10);
> There is an index created on id column, but query planner doesn't use it.
Have you declared myfunction as IMMUTABLE or STABLE? If it's volatile
then optimizing to an indexscan is incorrect and the planner won't do it.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|