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