Thread: Index not used when using a function

SELECT * FROM locations WHERE id = 12345 LIMIT 1

uses the primary key (id) index, but...

SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
LIMIT 1

does not and is verrry slow. Any ideas why? Whats weird is that it
works (uses index) on a previous db, but when I copied everything over
to a new db it doesnt. Ive ran vacuum + analyze, does the planner just
need more time to figure out that it needs to use an index?

Nick <nboutelier@gmail.com> writes:
> SELECT * FROM locations WHERE id = 12345 LIMIT 1
> uses the primary key (id) index, but...

> SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
> LIMIT 1

> does not and is verrry slow. Any ideas why?

You didn't mark the function stable or immutable.
http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html

            regards, tom lane

On Jan 13, 4:21 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Nick <nboutel...@gmail.com> writes:
> > SELECT * FROM locations WHERE id = 12345 LIMIT 1
> > uses the primary key (id) index, but...
> > SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
> > LIMIT 1
> > does not and is verrry slow. Any ideas why?
>
> You didn't mark the function stable or immutable.http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html
>
>                         regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

That was it, thank you.