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 4FB973A3B60 for ; Sun, 24 Oct 2004 20:09:08 +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 01845-07 for ; Sun, 24 Oct 2004 19:08:32 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 0DF983A2BE5 for ; Sun, 24 Oct 2004 20:08:58 +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 i9OJ92Mq027718; (envelope-from ) Sun, 24 Oct 2004 14:09:02 -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 i9OJ91BN028283; (envelope-from ) Sun, 24 Oct 2004 14:09:01 -0500 (CDT) Message-ID: <417BFDCB.3040404@johnmeinel.com> Date: Sun, 24 Oct 2004 14:08:59 -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 , pgsql-performance@postgresql.org Subject: Re: Queries slow using stored procedures References: <20041024184053.9819240619B@mra02.ex.eclipse.net.uk> In-Reply-To: <20041024184053.9819240619B@mra02.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="------------enigC39560A59387A6C5A347CC5D" 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/397 X-Sequence-Number: 8873 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigC39560A59387A6C5A347CC5D Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Rod Dutton wrote: > Thank John, > > I am running Postgres 7.3.7 on a Dell PowerEdge 6600 Server with Quad Xeon > 2.7GHz processors with 16GB RAM and 12 x 146GB drives in Raid 10 (OS, WAL, > Data all on separate arrays). > You might want think about upgraded to 7.4, as I know it is better at quite a few things. But I'm not all that experienced (I just had a similar problem). > I did try hard coding botnumber as you suggested and it was FAST. So it > does look like the scenario that you have explained. > There are 2 ways of doing it that I know of. First, you can make you function create a query and execute it. Something like: EXECUTE ''SELECT 1 FROM transbatch WHERE botnumber = '' || quote_literal(botnum) || '' LIMIT 1''; That forces the database to redesign the query each time. The problem you are having is a stored procedure has to prepare the query in advance. > >>does the column "botnumber" have the same value repeated many, many times, > > but '1-7' only occurs a few? > > Yes, that could be the case, the table fluctuates massively from small to > big to small regularly with a real mixture of occurrences of these values > i.e. some values are repeated many times and some occur only a few times. > > I wonder if the answer is to: a) don't use a stored procedure b) up the > statistics gathering for that column ? > I don't believe increasing statistics will help, as prepared statements require one-size-fits-all queries. > I will try your idea: select 1 where exist(select from transbatch where > botnumber = '1-7' limit 1); > > Also, how can I get "EXPLAIN" output from the internals of the stored > procedure as that would help me? > I believe the only way to get explain is to use prepared statements instead of stored procedures. For example: PREPARE my_plan(char(10)) AS SELECT 1 FROM transbatch WHERE botnumber = $1 LIMIT 1; EXPLAIN ANALYZE EXECUTE my_plan('1-7'); > Many thanks, > > Rod > If you have to do the first thing I mentioned, I'm not sure if you are getting much out of your function, so you might prefer to just ask the question directly. What really surprises me is that it doesn't use the index even after the LIMIT clause. But I just did a check on my machine where I had a column with lots of repeated entries, and it didn't use the index. So a question for the true Guru's (like Tom Lane): Why doesn't postgres use an indexed query if you supply a LIMIT? John =:-> --------------enigC39560A59387A6C5A347CC5D 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/3PJdeBCYSNAAMRAvjcAJ4l9eoNP1nU8Brgylbh5wIlB4gW2gCfVbNj uBgQNBZ4m+ZhGFxzDuk2JUk= =T1Dk -----END PGP SIGNATURE----- --------------enigC39560A59387A6C5A347CC5D--