pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Use of indexes in plpgsql functions
3+ messages / 2 participants
[nested] [flat]

* Use of indexes in plpgsql functions
@ 2000-12-15 18:36 Graham Vickrage <graham@digitalplanit.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Graham Vickrage @ 2000-12-15 18:36 UTC (permalink / raw)
  To: 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)

Attachments:

  winmail.dat (application/ms-tnef, 2.2K) download

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

* Use of indexes in plpgsql functions
@ 2000-12-15 22:05 Graham Vickrage <graham@digitalplanit.com>
  2000-12-15 22:54 ` Re: Use of indexes in plpgsql functions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Graham Vickrage @ 2000-12-15 22:05 UTC (permalink / raw)
  To: 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)
> 
> 
> 
> 
> 
> 

Attachments:

  winmail.dat (application/ms-tnef, 2.3K) download

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

* Re: Use of indexes in plpgsql functions
  2000-12-15 22:05 Use of indexes in plpgsql functions Graham Vickrage <graham@digitalplanit.com>
@ 2000-12-15 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-12-15 22:54 UTC (permalink / raw)
  To: Graham Vickrage <graham@digitalplanit.com>; +Cc: pgsql-sql

"Graham Vickrage" <graham@digitalplanit.com> writes:
>> 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;

>  [ is slow ]

A possible gotcha is if the url and website_id columns are declared as
something other than varchar and int4 respectively.  The planner's not
very smart about optimizing cross-datatype comparisons into indexscans.
When you write out a query with constants you are protected from this
because the constants have their types adjusted, but when you write "url
= $1" you'd best make sure $1 is declared exactly the same way as url.

7.1 fixes some instances of this gotcha, but not all of 'em IIRC.

			regards, tom lane



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


end of thread, other threads:[~2000-12-15 22:54 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-15 18:36 Use of indexes in plpgsql functions Graham Vickrage <graham@digitalplanit.com>
2000-12-15 22:05 Use of indexes in plpgsql functions Graham Vickrage <graham@digitalplanit.com>
2000-12-15 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>

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