pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Meinel <john@johnmeinel.com>
To: Rod Dutton <rod@e-rm.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Queries slow using stored procedures
Date: Sun, 24 Oct 2004 13:25:49 -0500
Message-ID: <417BF3AD.6010901@johnmeinel.com> (raw)
In-Reply-To: <20041024180640.F2D16133C61@mra04.ex.eclipse.net.uk>
References: <20041024180640.F2D16133C61@mra04.ex.eclipse.net.uk>

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
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (6+ messages)  latest in thread

Message-ID: <417BF3AD.6010901@johnmeinel.com>
Permalink:  ../417BF3AD.6010901@johnmeinel.com/
Also on:    postgresql.org/message-id/417BF3AD.6010901@johnmeinel.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: john@johnmeinel.com, rod@e-rm.co.uk
  Subject: Re: Queries slow using stored procedures
  In-Reply-To: <417BF3AD.6010901@johnmeinel.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox