pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Queries slow using stored procedures
6+ messages / 3 participants
[nested] [flat]

* Queries slow using stored procedures
@ 2004-10-18 18:01  Rod Dutton <rod@e-rm.co.uk>
  0 siblings, 1 reply; 6+ messages in thread

From: Rod Dutton @ 2004-10-18 18:01 UTC (permalink / raw)
  To: pgsql-performance

Hi,
 
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.
 
Help please!
 
This query is fast:-
 
explain analyze   
  SELECT batchserial
  FROM transbatch
  WHERE botnumber = '1-7'
  LIMIT 1;

                                                           QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------
 Limit  (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1
loops=1)
   ->  Index Scan using ind_tbatchx on transbatch  (cost=0.00..18.73 rows=45
width=4) (actual time=0.73..148.22 rows=1 loops=1)
         Index Cond: (botnumber = '1-7'::bpchar)
 Total runtime: 148.29 msec
(4 rows)
 
 
This function is slow:-
 
CREATE OR REPLACE FUNCTION  sp_test_rod3 ( ) returns integer          
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;
BEGIN
 
  bot := ''1-7'';
 
  SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE botnumber = bot
  LIMIT 1;
 
  IF FOUND THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
 
END;
'
language plpgsql  ;

 
explain analyze SELECT sp_test_rod3();

                                       QUERY PLAN

----------------------------------------------------------------------------
------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40
rows=1 loops=1)
 Total runtime: 1452.42 msec
(2 rows)

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Queries slow using stored procedures
@ 2004-10-19 07:25  Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
  parent: Rod Dutton <rod@e-rm.co.uk>
  0 siblings, 0 replies; 6+ messages in thread

From: Alban Medici (NetCentrex) @ 2004-10-19 07:25 UTC (permalink / raw)
  To: pgsql-performance

You seem to not have index on botnumber,  but in your query bot number is
the clause.
 
I don't explain you why the same query is so long.
but have your try procedure with a loop structure (witch create cursor) ?
 
you could try
 
 
CREATE OR REPLACE FUNCTION  sp_test_Alban1 ( ) returns integer          
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;
  rec RECORD;
  query VARCHAR;

BEGIN
 
  -- initialisation
  bot := ''1-7'';
  query  := '' SELECT  batchserial FROM transbatch WHERE botnumber  = ' ||
quote_ident(bot) || '' <optionaly your limit clause> ;'';
 
 
   FOR rec IN EXECUTE var_query  LOOP
        return rec."batchserial ".;    
   END LOOP;
    
    --else
    return 0;
 
END;
'
language plpgsql  ;

does it return the same results in the same time ? 

  _____  

From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rod Dutton
Sent: lundi 18 octobre 2004 20:01
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Queries slow using stored procedures


Hi,
 
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.
 
Help please!
 
This query is fast:-
 
explain analyze   
  SELECT batchserial
  FROM transbatch
  WHERE botnumber = '1-7'
  LIMIT 1;

                                                           QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------
 Limit  (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1
loops=1)
   ->  Index Scan using ind_tbatchx on transbatch  (cost=0.00..18.73 rows=45
width=4) (actual time=0.73..148.22 rows=1 loops=1)
         Index Cond: (botnumber = '1-7'::bpchar)
 Total runtime: 148.29 msec
(4 rows)
 
 
This function is slow:-
 
CREATE OR REPLACE FUNCTION  sp_test_rod3 ( ) returns integer          
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;
BEGIN
 
  bot := ''1-7'';
 
  SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE botnumber = bot
  LIMIT 1;
 
  IF FOUND THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
 
END;
'
language plpgsql  ;

 
explain analyze SELECT sp_test_rod3();

                                       QUERY PLAN

----------------------------------------------------------------------------
------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40
rows=1 loops=1)
 Total runtime: 1452.42 msec
(2 rows)

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Queries slow using stored procedures
@ 2004-10-24 18:13  Rod Dutton <rod@e-rm.co.uk>
  0 siblings, 1 reply; 6+ messages in thread

From: Rod Dutton @ 2004-10-24 18:13 UTC (permalink / raw)
  To: pgsql-performance

 
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.
 
Help please!
 
This query is fast:-
 
explain analyze   
  SELECT batchserial
  FROM transbatch
  WHERE botnumber = '1-7'
  LIMIT 1;

                                                           QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------
 Limit  (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1
loops=1)
   ->  Index Scan using ind_tbatchx on transbatch  (cost=0.00..18.73 rows=45
width=4) (actual time=0.73..148.22 rows=1 loops=1)
         Index Cond: (botnumber = '1-7'::bpchar)
 Total runtime: 148.29 msec
(4 rows)
 
 
This function is slow:-
 
CREATE OR REPLACE FUNCTION  sp_test_rod3 ( ) returns integer          
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;
BEGIN
 
  bot := ''1-7'';
 
  SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE botnumber = bot
  LIMIT 1;
 
  IF FOUND THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
 
END;
'
language plpgsql  ;

 
explain analyze SELECT sp_test_rod3();

                                       QUERY PLAN

----------------------------------------------------------------------------
------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40
rows=1 loops=1)
 Total runtime: 1452.42 msec
(2 rows)

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Queries slow using stored procedures
@ 2004-10-24 18:25  John Meinel <john@johnmeinel.com>
  parent: Rod Dutton <rod@e-rm.co.uk>
  0 siblings, 0 replies; 6+ messages in thread

From: John Meinel @ 2004-10-24 18:25 UTC (permalink / raw)
  To: Rod Dutton <rod@e-rm.co.uk>; +Cc: pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Queries slow using stored procedures
@ 2004-10-24 19:08  John Meinel <john@johnmeinel.com>
  0 siblings, 0 replies; 6+ messages in thread

From: John Meinel @ 2004-10-24 19:08 UTC (permalink / raw)
  To: Rod Dutton <rod@e-rm.co.uk>; pgsql-performance

Rod Dutton wrote:
> Thank John,
> 
> I am running Postgres 7.3.7 on a Dell PowerEdge 6600 Server with Quad Xeon
> 2.7GHz processors with 16GB RAM and 12 x 146GB drives in Raid 10 (OS, WAL,
> Data all on separate arrays).
> 

You might want think about upgraded to 7.4, as I know it is better at 
quite a few things. But I'm not all that experienced (I just had a 
similar problem).

> I did try hard coding botnumber as you suggested and it was FAST.  So it
> does look like the scenario that you have explained. 
> 

There are 2 ways of doing it that I know of. First, you can make you 
function create a query and execute it. Something like:

EXECUTE ''SELECT 1 FROM transbatch WHERE botnumber = ''
	|| quote_literal(botnum)
	|| '' LIMIT 1'';

That forces the database to redesign the query each time. The problem 
you are having is a stored procedure has to prepare the query in advance.

> 
>>does the column "botnumber" have the same value repeated many, many times,
> 
> but '1-7' only occurs a few?
> 
> Yes, that could be the case, the table fluctuates massively from small to
> big to small regularly with a real mixture of occurrences of these values
> i.e. some values are repeated many times and some occur only a few times.
> 
> I wonder if the answer is to: a) don't use a stored procedure b) up the
> statistics gathering for that column ?
> 

I don't believe increasing statistics will help, as prepared statements 
require one-size-fits-all queries.

> I will try your idea: select 1 where exist(select from transbatch where
> botnumber = '1-7' limit 1);
> 
> Also, how can I get "EXPLAIN" output from the internals of the stored
> procedure as that would help me?
> 

I believe the only way to get explain is to use prepared statements 
instead of stored procedures. For example:

PREPARE my_plan(char(10)) AS SELECT 1 FROM transbatch
	WHERE botnumber = $1 LIMIT 1;

EXPLAIN ANALYZE EXECUTE my_plan('1-7');


> Many thanks,
> 
> Rod
> 

If you have to do the first thing I mentioned, I'm not sure if you are 
getting much out of your function, so you might prefer to just ask the 
question directly.

What really surprises me is that it doesn't use the index even after the 
LIMIT clause. But I just did a check on my machine where I had a column 
with lots of repeated entries, and it didn't use the index.

So a question for the true Guru's (like Tom Lane):

Why doesn't postgres use an indexed query if you supply a LIMIT?

John
=:->

Attachments:

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Queries slow using stored procedures
@ 2004-10-24 19:11  John Meinel <john@johnmeinel.com>
  0 siblings, 0 replies; 6+ messages in thread

From: John Meinel @ 2004-10-24 19:11 UTC (permalink / raw)
  To: Rod Dutton <rod@e-rm.co.uk>; pgsql-performance

Rod Dutton wrote:
> I also should add that the sp is only slow when the table is big (probably
> obvious!).
> 
> Rod 

Sure, the problem is it is switching to a sequential search, with a lot 
of rows, versus doing an indexed search.

It's all about trying to figure out how to fix that, especially for any 
value of botnum. I would have hoped that using LIMIT 1 would have fixed 
that.

John
=:->

Attachments:

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-10-24 19:11 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-18 18:01 Queries slow using stored procedures Rod Dutton <rod@e-rm.co.uk>
2004-10-19 07:25 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
2004-10-24 18:13 Queries slow using stored procedures Rod Dutton <rod@e-rm.co.uk>
2004-10-24 18:25 ` John Meinel <john@johnmeinel.com>
2004-10-24 19:08 Re: Queries slow using stored procedures John Meinel <john@johnmeinel.com>
2004-10-24 19:11 Re: Queries slow using stored procedures John Meinel <john@johnmeinel.com>

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