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 database each time?

With the combined index, or just an index on each column; if you disable seqscans (set enable_seqscan to false), at what cost does the planner estimate the bitmap index scan that I expect you'll get in that case? Can you show us the output of 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 a seqscan. 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 useless here. 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!