Posted almost 2 years ago on February 18, 2012
There's nothing more frustrating than a slow running query, and that is exactly what I had while working with a PostgreSQL database recently. Actually it wasn't the query that was slow, but my implementation of PostgreSQL's functions. I'm documenting a solution here in hope that someone else will find this when having similar problems.
The query in question was extremely simplistic, similar to this:
SELECT t.* FROM some_table t WHERE name = 'xyz';
When I ran that guy from the command line I was getting response times right around 10 milliseconds on a table with 500k records, and an index on the name column. Pretty much expected.
Then I tried wrapping up that query into a function:
CREATE OR REPLACE FUNCTION test_function(name_param character(9)) RETURNS SETOF RECORD AS $BODY$ BEGIN RETURN QUERY SELECT t.* FROM some_table t WHERE name = 'xyz'; END; $BODY$ LANGUAGE plpgsql STABLE;
When I ran that function, the response time was around 500 milliseconds. Turned out that the problem was that the name column in some_table was defined as character varying(9), and the name_param input parameter was defined as character(9). This type mismatch completely threw off the query planner and caused my index to be ignored.