X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A87C33A3F93 for ; Sun, 24 Oct 2004 19:25:53 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94664-01 for ; Sun, 24 Oct 2004 18:25:21 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 53D993A3B60 for ; Sun, 24 Oct 2004 19:25:46 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.12.9/8.12.9) with ESMTP id i9OIPmMq027018; (envelope-from ) Sun, 24 Oct 2004 13:25:48 -0500 (CDT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by server11.icaen.uiowa.edu (8.12.9/smtp-service-1.6) with ESMTP id i9OIPlBN022250; (envelope-from ) Sun, 24 Oct 2004 13:25:47 -0500 (CDT) Message-ID: <417BF3AD.6010901@johnmeinel.com> Date: Sun, 24 Oct 2004 13:25:49 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rod Dutton Cc: pgsql-performance@postgresql.org Subject: Re: Queries slow using stored procedures References: <20041024180640.F2D16133C61@mra04.ex.eclipse.net.uk> In-Reply-To: <20041024180640.F2D16133C61@mra04.ex.eclipse.net.uk> X-Enigmail-Version: 0.86.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig14D8A891B7D53A803E849F3A" X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/396 X-Sequence-Number: 8872 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig14D8A891B7D53A803E849F3A Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Rod Dutton wrote: > > Hi, > > Has anybody got any ideas on my recent posting ? (thanks in advance) :- > > > I have a problem where a query inside a function is up to 100 times > slower inside a function than as a stand alone query run in psql. > > The column 'botnumber' is a character(10), is indexed and there are > 125000 rows in the table. > [...] I had a similar problem before, where the function version (stored procedure or prepared query) was much slower. I had a bunch of tables all with references to another table. I was querying all of the references to see if anyone from any of the tables was referencing a particular row in the base table. It turned out that one of the child tables was referencing the same row 300,000/500,000 times. So if I happened to pick *that* number, postgres wanted to a sequential scan because of all the potential results. In my testing, I never picked that number, so it was very fast, since it knew it wouldn't get in trouble. In the case of the stored procedure, it didn't know which number I was going to ask for, so it had to plan for the worst, and *always* do a sequential scan. So the question is... In your table, does the column "botnumber" have the same value repeated many, many times, but '1-7' only occurs a few? If you change the function to: CREATE OR REPLACE FUNCTION sp_test_rod3 ( ) returns integer as ' DECLARE bot char(10); oldbatch INTEGER; BEGIN SELECT INTO oldbatch batchserial FROM transbatch WHERE botnumber = ''1-7'' LIMIT 1; IF FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END; ' language plpgsql ; Is it still slow? I don't know if you could get crazy with something like: select 1 where exist(select from transbatch where botnumber = '1-7' limit 1); Just some thoughts about where *I've* found performance to change between functions versus raw SQL. You probably should also mention what version of postgres you are running (and possibly what your hardware is) John =:-> --------------enig14D8A891B7D53A803E849F3A Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBe/OtJdeBCYSNAAMRAp7GAKCorGfd2EOHXy1WplWCYrQ4B6LeRQCgiXzq QMz9AdKW8Ali61i+xQd0fVk= =UMbP -----END PGP SIGNATURE----- --------------enig14D8A891B7D53A803E849F3A--