Re: Index on immutable function call

Alban Hertroys wrote:
>> Hi,
>>
>> It does!
>>
>> With your index alone:
>>
>> ---------------------------
>> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=33333
>> width=12) (actual time=7.796..236.722 rows=50116 loops=1) Recheck
>> Cond: (data1 > this_is_a_long_transformation(data2))
>> -> Bitmap Index Scan on transform_index (cost=0.00..806.84
>> rows=33333 width=0) (actual time=7.665..7.665 rows=50116 loops=1)
>> Total runtime: 459.380 ms ---------------------------
>>
>> That works just fine, but is there maybe a way of creating a slighly
>> more "generic" index? If I change the ">" with a "<" in the query,
>> index cannot of course be used. According to documentation, answer
>> seems to be "no"...
>
> I have to say I'm a bit surprised this works, as the database still
> needs to check all these rows for existence. Apparently the index is
> sufficiently selective with your database tuning parameters.
>
> That said, if this works then a combined index on (data1,
> this_is_a_long_transformation(data2)) will probably also work and
> give you the flexibility you need.

I have tried with a combined index:

create index long_transformation_index on indexed_table (data1,
this_is_a_long_transformation(data2));

Unfortunately, it does not work:

-------------------------------
Seq Scan on indexed_table (cost=0.00..26791.00 rows=33333 width=12)
(actual time=0.327..5805.199 rows=49959 loops=1)
Filter: (data1 > this_is_a_long_transformation(data2))
Total runtime: 6340.772 ms
-------------------------------

-----------------------------------------------------------------------
Philippe Lang Web : www.attiksystem.ch
Attik System Email : philippe(dot)lang(at)attiksystem(dot)ch
rte de la Fonderie 2 Phone : +41 26 422 13 75
1700 Fribourg Mobile : +41 79 351 49 94
Switzerland Fax : +41 26 422 13 76