Обсуждение: 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.