From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | "Alban Hertroys" <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Index on immutable function call |
Date: | 2010-01-19 09:38:41 |
Message-ID: | E6A0649F1FBFA3408A37F505400E7AC21F8A7D@email.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
pgsql-general-owner(at)postgresql(dot)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(dot)lang(at)attiksystem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Philippe Lang | 2010-01-19 09:52:50 | Re: Index on immutable function call |
Previous Message | A. Kretschmer | 2010-01-19 09:38:22 | Re: Index on immutable function call |