Re: Bad plan when join on function

2011/1/17 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
> Zotov  wrote:
>
>> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
>> Why SeqScan???
>
> Because you don't have an index on AsInteger(c.id).
>
> If your function is IMMUTABLE (each possible combination of input
> values always yields the same result), and you declare it such, then
> you can index on the function, and it will perform at a speed similar
> to the other example.

it should to work without functional index - but not sure about effectivity

postgres=# explain select 1 from a join b on a.f = sin(b.f);
QUERY PLAN
-----------------------------------------------------------------------------
Merge Join (cost=809.39..1352.64 rows=10000 width=0)
Merge Cond: (a.f = (sin(b.f)))
-> Index Scan using a_f_idx on a (cost=0.00..318.25 rows=10000 width=8)
-> Sort (cost=809.39..834.39 rows=10000 width=8)
Sort Key: (sin(b.f))
-> Seq Scan on b (cost=0.00..145.00 rows=10000 width=8)
(6 rows)

but functional index always helps

postgres=# create index on b((sin(f)));
CREATE INDEX
postgres=# explain select 1 from a join b on a.f = sin(b.f);
QUERY PLAN
-------------------------------------------------------------------------------
Merge Join (cost=0.00..968.50 rows=10000 width=0)
Merge Cond: (a.f = sin(b.f))
-> Index Scan using a_f_idx on a (cost=0.00..318.25 rows=10000 width=8)
-> Index Scan using b_sin_idx on b (cost=0.00..450.25 rows=10000 width=8)
(4 rows)

regards

Pavel Stehule
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>