Rod Dutton wrote:
>
> Hi,
>
> Has anybody got any ideas on my recent posting ? (thanks in advance) :-
>
>
> I have a problem where a query inside a function is up to 100 times
> slower inside a function than as a stand alone query run in psql.
>
> The column 'botnumber' is a character(10), is indexed and there are
> 125000 rows in the table.
>
[...]
I had a similar problem before, where the function version (stored
procedure or prepared query) was much slower. I had a bunch of tables
all with references to another table. I was querying all of the
references to see if anyone from any of the tables was referencing a
particular row in the base table.
It turned out that one of the child tables was referencing the same row
300,000/500,000 times. So if I happened to pick *that* number, postgres
wanted to a sequential scan because of all the potential results. In my
testing, I never picked that number, so it was very fast, since it knew
it wouldn't get in trouble.
In the case of the stored procedure, it didn't know which number I was
going to ask for, so it had to plan for the worst, and *always* do a
sequential scan.
So the question is... In your table, does the column "botnumber" have
the same value repeated many, many times, but '1-7' only occurs a few?
If you change the function to:
CREATE OR REPLACE FUNCTION sp_test_rod3 ( ) returns integer
as '
DECLARE
bot char(10);
oldbatch INTEGER;
BEGIN
SELECT INTO oldbatch batchserial
FROM transbatch
WHERE botnumber = ''1-7''
LIMIT 1;
IF FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
'
language plpgsql ;
Is it still slow?
I don't know if you could get crazy with something like:
select 1 where exist(select from transbatch where botnumber = '1-7'
limit 1);
Just some thoughts about where *I've* found performance to change
between functions versus raw SQL.
You probably should also mention what version of postgres you are
running (and possibly what your hardware is)
John
=:->