Skip site navigation (1) Skip section navigation (2)

Use of indexes in plpgsql functions

From: "Graham Vickrage" <graham(at)digitalplanit(dot)com>
To: "POSTGRES" <pgsql-sql(at)postgresql(dot)org>
Subject: Use of indexes in plpgsql functions
Date: 2000-12-15 18:36:39
Message-ID: NDBBJABDILOPAOOMFJHOGEGOCGAA.graham@digitalplanit.com (view raw or flat)
Thread:
Lists: pgsql-sql
I have a table with 650k rows with an index on URL (pg v7.0.0 on
i686-pc-linux-gnu)

When using psql the select query behaves as expected i.e. takes < 1 second
(and explain tells me it is using the correct index)

However when I put this into a pl function it takes about 2.5 mins, Has
anyone had any similar problems/solutions or is it just that I am over
looking something??? (I know there is an update but again when executed
seperately it takes approx 1 sec)

Regards 

Graham

details as follows: -

SELECT now(); SELECT count(*) FROM statistics WHERE url ='XXX' and
website_id =1035; SELECT now();

now
----------------------
2000-12-15 19:17:34+00

count
-----
    421
(1 row)

now
----------------------
2000-12-15 19:17:35+00
(1 row)

CREATE FUNCTION get_url_hits (varchar, int4) RETURNS int4 AS '
DECLARE
	num INT4;
BEGIN
	SELECT count(*) INTO num FROM statistics WHERE url = $1 and
website_id = $2;
	
	IF num > 0 THEN
		UPDATE site_url SET hits = num, last_updated = now() where
website_id = $2 and url = $1;
	END IF;
RETURN num;
END;' LANGUAGE 'plpgsql';

select now(); select get_url_hits ('XXX', 1001); select now(); 

now
----------------------
2000-12-15 19:21:40+00
(1 row)

get_url_hits
------------
      421
(1 row)

now
----------------------
2000-12-15 19:24:06+00
(1 row)







Attachment: winmail.dat
Description: application/ms-tnef (2.2 KB)

pgsql-sql by date

Next:From: Bruce MomjianDate: 2000-12-15 18:37:16
Subject: Re: postgres
Previous:From: Artur RatajDate: 2000-12-15 18:33:56
Subject: Re: full text index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group