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.