pgsql-general-ow...@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.l...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
