Re: Index on immutable function call

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


Strange. I noticed that the number of records you get from each method differs somewhat, are you recreating the
databaseeach time? 

With the combined index, or just an index on each column; if you disable seqscans (set enable_seqscan to false), at
whatcost does the planner estimate the bitmap index scan that I expect you'll get in that case? Can you show us the
outputof explain for that case? 

I don't get why it'd be estimated so much more expensive than the partial index Tore came up with that it would prefer
aseqscan. Tore's index would create a better balanced tree as serial is guaranteed to be unique, while data1 and data2
aren't(collisions). 

It's all probably an artefact of the randomness of your data - many of the statistics the planner tracks are quite
uselesshere. Real data tends to be a lot less random so estimates are usually much better there. 

Alban Hertroys

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


!DSPAM:737,4b55902010601090241314!