Re: Bad plan when join on function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2011/1/17 Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux