Re: Index on immutable function call

> 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.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b55875b10601514515279!