Use of indexes in plpgsql functions - Mailing list pgsql-sql

From Graham Vickrage
Subject Use of indexes in plpgsql functions
Date
Msg-id NDBBJABDILOPAOOMFJHOGEGOCGAA.graham@digitalplanit.com
Whole thread Raw
List 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

pgsql-sql by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [Re: postgres]
Next
From: Artur Rataj
Date:
Subject: full text index