pgsql-general-owner@postgresql.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.lang@attiksystem.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
