At 14:44, Thu 2012-11-15, Jeff Davis wrote: […] > It's possible that the function call may be unnecessary, but that is > more of a performance enhancement, not a bug.
For me it's a matter of orders of size of performance, so it feels like a bug ;p > Also, the example function has side effects. If you declare functions > with side effects to be IMMUTABLE, you can get all kinds of problems. > You should certainly not rely on an IMMUTABLE function to be called a > specific number of times. The function was only a minimal example, not what I'm running in my production code =) At 11:40, Fri 2012-11-16, Stuart Bishop wrote: > I think this is the same issue as was discussed here, dating from > PostgreSQL 8.1: > > http://postgresql.1045698.n5.nabble.com/Slow-functional-indexes-td2059587.html Yup, looks like the same thing. Would be nice to have this fixed. Did, however, find two workarounds possible from PostgreSQL 9.2: 1. Create an index over all the columns you want. The index-only scan eliminates recalculation 2. Create an index over (my_complex_function(whatever_columns), table_primary_key) and wrap the query as such: SELECT a_lot_of_columns FROM my_table WHERE table_primary_key IN (SELECT table_primary_key FROM my_table ORDER BY my_complex_function(whatever_columns)); Did stumble across a situation where the function was rerun for *all* rows at some point *despite* having such an index. Will see if I can reproduce that if I have the time! Smiles, -- Morten Minde Neergaard -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
