Re: Index on immutable function call

pgsql-general-owner(at)postgresql(dot)org wrote:
> Hello Philippe,
>
> if you always select data1 > this_is_a_long_transformation(data2) you
> could use the following index:
>
>
> create index long_transformation_index_2 on indexed_table ( ( data1 >
> this_is_a_long_transformation(data2) ) );
>
>
>
> Index Scan using long_transformation_index_2 on indexed_table
> (cost=0.25..2450.96 rows=33333 width=12)
> Index Cond: ((data1 > this_is_a_long_transformation(data2)) = true)
> Filter: (data1 > this_is_a_long_transformation(data2))

Hi Timo,

Thanks, that was certainly what I was searching for...

I tried your solution, but it's slower than the partial index:

1) Index
--------

create index long_transformation4_index on indexed_table ( ( data1 >
this_is_a_long_transformation(data2) ) );
------------------------------
"Index Scan using long_transformation4_index on indexed_table
(cost=0.25..3466.51 rows=33333 width=12) (actual time=0.252..3125.308
rows=50281 loops=1)"
" Index Cond: ((data1 > this_is_a_long_transformation(data2)) = true)"
" Filter: (data1 > this_is_a_long_transformation(data2))"
"Total runtime: 3505.435 ms"
------------------------------

2) Partial index
----------------

create index transform_index on indexed_table(id) where data1 >
this_is_a_long_transformation(data2);
------------------------------
"Bitmap Heap Scan on indexed_table (cost=815.09..10106.01 rows=33333
width=12) (actual time=7.477..237.331 rows=50101 loops=1)"
" Recheck Cond: (data1 > this_is_a_long_transformation(data2))"
" -> Bitmap Index Scan on transform_index (cost=0.00..806.76
rows=33333 width=0) (actual time=7.339..7.339 rows=50101 loops=1)"
"Total runtime: 459.657 ms"
------------------------------

I guess it's because the partial index is smaller?

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