Re: Index on immutable function call

pgsql-general-owner@postgresql.org wrote:

> Did you analyse the table?

Hi,

Yes, I did.

> Can you show us an explain analyse?

Here is the explain with my initial index:

create index long_transformation2_index on indexed_table
(this_is_a_long_transformation(data2));

-------------------------------------
Seq Scan on indexed_table  (cost=0.00..26791.00 rows=33333 width=12)
(actual time=0.158..5379.933 rows=49740 loops=1)
  Filter: (data1 > this_is_a_long_transformation(data2))
Total runtime: 5606.855 ms
-------------------------------------

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

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