Re: Index on immutable function call

On 19 Jan 2010, at 10:38, Philippe Lang wrote:

>> What I notice off-hand is that you don't appear to have an index on
>> data1, so Postgres doesn't know for which rows that is >
>> some_immutable_function(data2).
>
> I tried adding an index on data1:
>
> create index long_transformation1_index on indexed_table (data1);
> create index long_transformation2_index on indexed_table
> (this_is_a_long_transformation(data2));
>
> But I still have an sequential scan:
>
> -------------------------------------
> Seq Scan on indexed_table (cost=0.00..26791.00 rows=33333 width=12)
> (actual time=0.199..5284.322 rows=49739 loops=1)
> Filter: (data1 > this_is_a_long_transformation(data2))
> Total runtime: 5513.676 ms
> -------------------------------------

Ah yes, you would. I did kind of expect this answer, but this confirms it.
About every other row matches your query. Although the planner thinks every one out of three does, that still means a sequential scan is probably going to be faster than an index scan.

I'm quite sure you would get an index scan if you'd reduce the number of rows that match your query significantly, for example by querying for data1 * 100 > this_is...

Alban Hertroys

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

!DSPAM:737,4b55861110603998611157!