Received: from digitalplanit.wdi.co.uk ([195.13.87.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBFM6r616207 for ; Fri, 15 Dec 2000 17:06:53 -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 WAA08334 for ; Fri, 15 Dec 2000 22:02:44 GMT From: "Graham Vickrage" To: Subject: Use of indexes in plpgsql functions Date: Fri, 15 Dec 2000 22:05:01 -0000 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_001A_01C066E3.12174C60" 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/177 X-Sequence-Number: 511 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01C066E3.12174C60 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_001A_01C066E3.12174C60 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IgEWAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcA GAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEI AAUABAAAAAAAAAAAAAEJAAQAAgAAAAAAAAABBoADAA4AAADQBwwADwAWAAQA AAAFABEBAQOQBgDMCAAAKAAAAAsAAgABAAAACwAjAAAAAAADACYAAAAAAAsA KQAAAAAAAwAuAAAAAAADADYAAAAAAB4ATQABAAAAAQAAAAAAAAAeAHAAAQAA ACQAAABVc2Ugb2YgaW5kZXhlcyBpbiBwbHBnc3FsIGZ1bmN0aW9ucwACAXEA AQAAABsAAAABwGbF8zkR8Y290dER1JAWAEhUUB+2AAdDvyAACwAXDAAAAAAC AR0MAQAAAB4AAABTTVRQOkdSQUhBTUBESUdJVEFMUExBTklULkNPTQAAAAsA AQ4AAAAAQAAGDgAIhu3iZsABAgEKDgEAAAAYAAAAAAAAAIunWrxGyNMRkBMA SFRQH7bCgAAACwAfDgEAAAACAQkQAQAAAEAEAAA8BAAAUAcAAExaRnUq5sZi AwAKAHJjcGcxMjUmMgD4C2BuZwHQNTePAfcCpAPjAgBwcnEOUAhmY2gKwHNl dDDwIFRhaANxAoMAUBBmVxFHBxMCgH0KgXYIkHfSawuAZDQMYGMAUAsDQGZp LTE0NAFAbB5pFcMM0BXEDxExMDJTFOAQ4G90BZB0C6Qx/jYKogqCF5QBQAsE F5UPBiMSZAHQIEkgEVB2ZWggYSABkWwboAPwdIBoIDY1MGsgA2AedwQgHEMD kRSxZXgggQIgIFVSTCAoDgBgIHY3LjAe4B4CaYA2ODYtcGMtFhCgbnV4LWcg ACkYdskYkldoCfAgdQCQDyDwIHBzcQMgHGAboBGAExwQF9AgcQpQcnkgbGJl G3IEIGEEIB3gcEcXwQmAHaAuZS4b0WtxB5E8IDEicQWgFMAgfx5wAHAkcCQB C2Ib0CKQbP8EIAeAHaAFQAQAIYUiQgWhrwlwIsEdsyB7SBzwZRuQzQXAdyFS G1BwdQVAHGAzJ5ELgHRvG7ELUCBm9HVuF9BpHzIrESTzAaBjCGAFQDIuNScg C4Bz2CwgSCPRAHB5AiAboP8RUCRwLoEicAdwAxAKwReB4RwBbXMvcwbwKwAs cW8EIAWxK1IFQGohkCsSYXMFQBtQYW0eACpCCQBvvxShHqAwgAeAKzEPID80 APMecBtQa24c8CIyCXAngrEDkXVwZDIgG6BiKwH8YWcmkiqDHeAFkCsAJGFr EYAkIHI1sWwjMCy4cM8XgR3wJWMge1JlNkALINsEISCZRzewEVBtIIoBAD8B kAMQI7MCECbwHPE6IAItIIpTRUxFQ1TiIDSBKCk7BgA+xAWgASwwdCgqKSBG UvxPTSJwAZAsYDHQDeAEIKBXSEVSRSGAcgMg6D0nWEJQJx1xJHAqIKZiAJAX sF9pJHA9FzB8MzU/dz8kIIo0gRh0Lb9GL0ZyGHQB0EfwFbAyFbABLcAxOTox Nzoz/DQrR/AgikADRckYdCXg2SXgNDIVIwqAKCVgHOHfIHtFn0avR79IwjVJ F0w/JQqAQ0HAQVRB0EZVgk4+8ElPTiBnEZAqX0HxXytAdAQgKHZvCsARQi4g K4E0QHBBwFS9HkBOBfBVEhOgBfAnGHTyRD7gTEFBwBh1AZE/EEJ1MoBJTlQ0 RMVCvEVHWEBXWT+eWEFPV/NzQJ9BqCAkJWBCrl0wMidExgySV2hJRlfzPiCP EbFBoFk5V7NVUERS0v9DE0HyPrA/AFQjXSBYAS4g/wtgMdBT0DWTXkI/Iypy NPGvXb1dY1zmXqkgYNBEX/HfRMVVdFfyRMVoETtCgFcQoE5HVUFHQdAnC1D7 DgAiASdEyyKFPyUihVOs50JDLiAXMDAxbPg/JUyv702/Ts9ICkuwOhXgUO8g mf9TqnEvSvlLj0yfdw9yT3Nc8DQ6MDZ0fyCZf3+AiwUUIQCB8AsAAYAIIAYA AAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwADgAggBgAAAAAAwAAAAAAAAEYA AAAAEIUAAAAAAAADAAeACCAGAAAAAADAAAAAAAAARgAAAABShQAAJ2oBAB4A CYAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADkuMAAeAAqACCAG AAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgALgAggBgAAAAAA wAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4ADIAIIAYAAAAAAMAAAAAA AABGAAAAADiFAAABAAAAAQAAAAAAAAALAA2ACCAGAAAAAADAAAAAAAAARgAA AACChQAAAQAAAAsAOoAIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAAAwA8 gAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAD2ACCAGAAAAAADAAAAA AAAARgAAAAAYhQAAAAAAAAsAUoAIIAYAAAAAAMAAAAAAAABGAAAAAAaFAAAA AAAAAwBTgAggBgAAAAAAwAAAAAAAAEYAAAAAAYUAAAAAAAACAfgPAQAAABAA AACLp1q8RsjTEZATAEhUUB+2AgH6DwEAAAAQAAAAi6davEbI0xGQEwBIVFAf tgIB+w8BAAAAggAAAAAAAAA4obsQBeUQGqG7CAArKlbCAABQU1RQUlguRExM AAAAAAAAAABOSVRB+b+4AQCqADfZbgAAAEM6XFdJTkRPV1NcTG9jYWwgU2V0 dGluZ3NcQXBwbGljYXRpb24gRGF0YVxNaWNyb3NvZnRcT3V0bG9va1xvdXRs b29rLnBzdAAAAAMA/g8FAAAAAwANNP03AAACAX8AAQAAADgAAAA8TkRCQkpB QkRJTE9QQU9PTUZKSE9JRUhBQ0dBQS5ncmFoYW1AZGlnaXRhbHBsYW5pdC5j b20+AAMABhAebmVHAwAHEAoEAAADABAQAQAAAAMAERAGAAAAHgAIEAEAAABl AAAASUhBVkVBVEFCTEVXSVRINjUwS1JPV1NXSVRIQU5JTkRFWE9OVVJMKFBH VjcwME9OSTY4Ni1QQy1MSU5VWC1HTlUpV0hFTlVTSU5HUFNRTFRIRVNFTEVD VFFVRVJZQkVIQVZFUwAAAAAJJA== ------=_NextPart_000_001A_01C066E3.12174C60--