Dean Rasheed writes:
> 2010/1/19 Philippe Lang :
>> That works just fine, but is there maybe a way of creating a slighly
>> more "generic" index? If I change the ">" with a "<" in the query, index
>> cannot of course be used. According to documentation, answer seems to be
>> "no"...
> You could c
2010/1/19 Philippe Lang :
> That works just fine, but is there maybe a way of creating a slighly
> more "generic" index? If I change the ">" with a "<" in the query, index
> cannot of course be used. According to documentation, answer seems to be
> "no"...
>
You could create an index on the differ
pgsql-general-ow...@postgresql.org wrote:
> On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
>> I'm trying to figure out how to use an index on an immutable function
>> call in order to speed up queries.
> [..]
>> Unfortunately, Postgreql does not use the index at all.
>
> Yup, an in
pgsql-general-ow...@postgresql.org wrote:
> Hello Philippe,
>
> if you always select data1 > this_is_a_long_transformation(data2) you
> could use the following index:
>
>
> create index long_transformation_index_2 on indexed_table ( ( data1 >
> this_is_a_long_transformation(data2) ) );
>
>
>
On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
[..]
> Unfortunately, Postgreql does not use the index at all.
Yup, an index isn't going to be very useful in what you're doin
On Tue, Jan 19, 2010 at 11:57 AM, Alban Hertroys
> 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 f
> 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=3 width=12)
> (ac
l-general-ow...@postgresql.org] Im Auftrag von Philippe Lang
Gesendet: Dienstag, 19. Januar 2010 11:19
An: A. Kretschmer; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Index on immutable function call
pgsql-general-ow...@postgresql.org wrote:
> In response to Philippe Lang :
>>
>&g
pgsql-general-ow...@postgresql.org wrote:
> In response to Philippe Lang :
>>
>> My goal is to make query...
>>
>> select * from indexed_table WHERE data1 >
>> this_is_a_long_transformation(data2);
>>
>> ... as fast as
>>
>> select * from indexed_table WHERE data1 > data2;
>>
>> ... with t
pgsql-general-ow...@postgresql.org wrote:
> In response to Philippe Lang :
>>
>> My goal is to make query...
>>
>> select * from indexed_table WHERE data1 >
>> this_is_a_long_transformation(data2);
>>
>> ... as fast as
>>
>> select * from indexed_table WHERE data1 > data2;
>>
>> ... with t
Alban Hertroys wrote:
>> Hi,
>>
>> It does!
>>
>> With your index alone:
>>
>> ---
>> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=3
>> width=12) (actual time=7.796..236.722 rows=50116 loops=1) Recheck
>> Cond: (data1 > this_is_a_long_transformation
In response to Philippe Lang :
>
> My goal is to make query...
>
> select * from indexed_table WHERE data1 >
> this_is_a_long_transformation(data2);
>
> ... as fast as
>
> select * from indexed_table WHERE data1 > data2;
>
> ... with the help of the index "long_transformation_index".
>
A
> Hi,
>
> It does!
>
> With your index alone:
>
> ---
> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=3
> width=12) (actual time=7.796..236.722 rows=50116 loops=1)
> Recheck Cond: (data1 > this_is_a_long_transformation(data2))
> -> Bitmap Index Sca
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
pgsql-general-ow...@postgresql.org wrote:
> On Tue, Jan 19, 2010 at 10:22 AM, Alban Hertroys
> wrote:
>>
>> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
>>> The planner doesn't know the actual parameter for the function, so
>>> he picked out the wrong plan. You can force the planner to
>>> re-p
pgsql-general-ow...@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));
---
In response to Alban Hertroys :
>
> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
> > The planner doesn't know the actual parameter for the function, so he
> > picked out the wrong plan. You can force the planner to re-planning with
> > a dynamical statement within the function and EXECUTE that.
>
On Tue, Jan 19, 2010 at 10:22 AM, Alban Hertroys
wrote:
>
> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
>> The planner doesn't know the actual parameter for the function, so he
>> picked out the wrong plan. You can force the planner to re-planning with
>> a dynamical statement within the functio
On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
> The planner doesn't know the actual parameter for the function, so he
> picked out the wrong plan. You can force the planner to re-planning with
> a dynamical statement within the function and EXECUTE that.
There are no queries in his function, I t
On 19 Jan 2010, at 7:40, Philippe Lang wrote:
> Hi,
>
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
>
> I came up with this small test:
>
>
> --create database foo;
>
> --drop table indexed_
In response to Philippe Lang :
> Hi,
>
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
> My goal is to make query...
>
> select * from indexed_table WHERE data1 >
> this_is_a_long_transformation(data2);
>
> ... as fast as
>
> sele
Hi,
I'm trying to figure out how to use an index on an immutable function
call in order to speed up queries.
I came up with this small test:
--create database foo;
--drop table indexed_table;
create table indexed_table (
id serial primary key,
data1
22 matches
Mail list logo
