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 '
  bot char(10);
  oldbatch INTEGER;

  SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE botnumber = ''1-7''
  LIMIT 1;

    RETURN 1;
    RETURN 0;

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)


Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to