Re: BUG #7651: Superfluous calls to functions used for indexing

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