Received: from digitalplanit.wdi.co.uk ([195.13.87.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBFIcd021356 for ; Fri, 15 Dec 2000 13:38:39 -0500 (EST) (envelope-from graham@digitalplanit.com) Received: from Graham (dp0002.demon.co.uk [193.237.187.164]) by digitalplanit.wdi.co.uk (8.9.3/8.9.3) with SMTP id SAA06678 for ; Fri, 15 Dec 2000 18:34:20 GMT From: "Graham Vickrage" To: "POSTGRES" Subject: Use of indexes in plpgsql functions Date: Fri, 15 Dec 2000 18:36:39 -0000 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0015_01C066C5.F6548AA0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 Importance: Normal X-MS-TNEF-Correlator: X-Archive-Number: 200012/175 X-Sequence-Number: 509 This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C066C5.F6548AA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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) ------=_NextPart_000_0015_01C066C5.F6548AA0 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IicSAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcA GAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEG gAMADgAAANAHDAAPABIAJAAAAAUALQEBA5AGAGQIAAAlAAAACwACAAEAAAAL ACMAAAAAAAMAJgAAAAAACwApAAAAAAADADYAAAAAAB4AcAABAAAAJAAAAFVz ZSBvZiBpbmRleGVzIGluIHBscGdzcWwgZnVuY3Rpb25zAAIBcQABAAAAFgAA AAHAZsXzORHxjb3R0RHUkBYASFRQH7YAAAIBHQwBAAAAHgAAAFNNVFA6R1JB SEFNQERJR0lUQUxQTEFOSVQuQ09NAAAACwABDgAAAABAAAYOACjd3sVmwAEC AQoOAQAAABgAAAAAAAAAi6davEbI0xGQEwBIVFAftsKAAAALAB8OAQAAAAIB CRABAAAA+gMAAPYDAADYBgAATFpGdRcDSBsDAAoAcmNwZzEyNSYyAPgLYG5n AdA1N08B9wKkA+MCAGNoCsBzsGV0MCAHEwKAfQqBknYIkHdrC4BkNAxgjmMA UAsDC7UgSSAQ8KB2ZSBhIAGRbBQgAQPwdGggNjUwa3ogA2B3BCAUwwOREnFl BHggAiAgVVJMIIIoDgAgdjcuMBdgARaCaTY4Ni1wY4QtbAuAdXgtZxiATikK ogqECoBXaAnwIIZ1AJAPICBwc3EDIK8U4BQgESAUkGMFQHEKUGByeSBiZRPy BCBhOwQgFmBwGzEJgBYgLmWKLhRRaweRPCAxGvH7BaASgCAW8ABwHPAcgQti +xRQGxBsBCAHgBYgBUAEAH8aBRrCBaEJcBtBFjMY+0htFXBlFBAFwHcZ0hPQ cJ51BUAU4CARC4B0bxQxoQtQIGZ1bhtAaReyHyORHXMBoAhgBUAyLjXDH6AL gHMsIEgcUQBw/nkCIBQgEPAc8CcBGvAHcBsDEArBcANgFIFtcy/+cwbwI4Ak 8QQgBbEj0gVANmoaECOSYQVAE9BhbfcWgCLCCQBvEmEXICkAB4A7I7EPID8s gBbwE9Brbh8VcBqyCXAgAgORdXBkyyqgFCBiI4FhZx8SIwO/FmAFkCOAHOER IBygci4x9mwbsCU4cChxFnAd4xj7vFJlLsALIAQhGRlHMDD9EPBtGQoBAAGQ AxAcMwIQIx9wFXE6IC0ZClNFIExFQ1QgLQEoKR47BgA3RAWgJLB0KCrAKSBG Uk9NGvABkA8k4CpQDeAEIFdIRVKKRRoAcgMgPSdYOtBuJxXxHPAioGIAkBzQ X4JpHPA9MTAzNTf37zekGQotARkELT6vPvIZBDsB0EBwLQ4gQKAmQDE5gDox NzozNCtAcD8ZCjiDPkkZBB5gHmA0MvsS4wqAKB3gFWEY+z4fPy+7QD9BQjVB l0S/CoBDOkBEQVQ6UEZVTjdwSVBPTiBnETBfOnFf5SPAdAQgKHYKwBDiJqCt JAE0OPA6QFQWwE4F8FdNkhFgBfAnGQREN2BMXkE6QBkFAZE3kHUrAEmITlQ0 PUVCRUdQwHdP2TgeUMFPUHM5HzooIO4kHeA7LlWwMj1GDJJP6KRJRlBzPiAR UFQ6IONRuVAzVVBES1I7kzpy/zcwN4BMo1WgUIEmoAtgKlD/TFAuE1bCN6Mi 8i1xVj1V4+tVZlcpIFlQRFhxPUVN9DdQcj1FYJE7OwBPkE5H6FVBRzpQJwtQ DgAagf4nPUsbBTelGwVMLDrDJqD5PCAwMWV4N6VFL0Y/R0/jQIpEMDo0MElv GRlMKn9pr0N5RA9FH2+Pas9r3DT4OjA2bP8ZGXf/eQsR4QIAenAAAAsAAYAI IAYAAAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwADgAggBgAAAAAAwAAAAAAA AEYAAAAAEIUAAAAAAAADAAeACCAGAAAAAADAAAAAAAAARgAAAABShQAAJ2oB AB4ACYAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADkuMAAeAAqA CCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgALgAggBgAA AAAAwAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4ADIAIIAYAAAAAAMAA AAAAAABGAAAAADiFAAABAAAAAQAAAAAAAAALAA2ACCAGAAAAAADAAAAAAAAA RgAAAACChQAAAQAAAAsAOoAIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAA AwA8gAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAD2ACCAGAAAAAADA AAAAAAAARgAAAAAYhQAAAAAAAAsAUoAIIAYAAAAAAMAAAAAAAABGAAAAAAaF AAAAAAAAAwBTgAggBgAAAAAAwAAAAAAAAEYAAAAAAYUAAAAAAAACAfgPAQAA ABAAAACLp1q8RsjTEZATAEhUUB+2AgH6DwEAAAAQAAAAi6davEbI0xGQEwBI VFAftgIB+w8BAAAAggAAAAAAAAA4obsQBeUQGqG7CAArKlbCAABQU1RQUlgu RExMAAAAAAAAAABOSVRB+b+4AQCqADfZbgAAAEM6XFdJTkRPV1NcTG9jYWwg U2V0dGluZ3NcQXBwbGljYXRpb24gRGF0YVxNaWNyb3NvZnRcT3V0bG9va1xv dXRsb29rLnBzdAAAAAMA/g8FAAAAAwANNP03AAACAX8AAQAAADgAAAA8TkRC QkpBQkRJTE9QQU9PTUZKSE9HRUdPQ0dBQS5ncmFoYW1AZGlnaXRhbHBsYW5p dC5jb20+AAMABhAebmVHAwAHEAoEAAADABAQAAAAAAMAERAAAAAAHgAIEAEA AABlAAAASUhBVkVBVEFCTEVXSVRINjUwS1JPV1NXSVRIQU5JTkRFWE9OVVJM KFBHVjcwME9OSTY4Ni1QQy1MSU5VWC1HTlUpV0hFTlVTSU5HUFNRTFRIRVNF TEVDVFFVRVJZQkVIQVZFUwAAAADn+g== ------=_NextPart_000_0015_01C066C5.F6548AA0--