X-Original-To: pgsql-hackers-win32-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 55F0232A0A0; Wed, 29 Sep 2004 15:58:09 +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 70454-07; Wed, 29 Sep 2004 14:57:37 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 60EE7329E4A; Wed, 29 Sep 2004 15:57:22 +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 i8TEv6tq027140; sent by ; Wed, 29 Sep 2004 09:57:06 -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 i8TEuZ4J025624; (envelope-from ) Wed, 29 Sep 2004 09:57:06 -0500 (CDT) Message-ID: <415ACD1B.1020003@johnmeinel.com> Date: Wed, 29 Sep 2004 09:56:27 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: pgsql-hackers-win32 , pgsql-performance@postgresql.org Subject: Re: [PERFORM] Poor Performance for large queries References: <4155C955.40808@dunslane.net> <415A288F.2030002@johnmeinel.com> <22682.1096434647@sss.pgh.pa.us> <415A575F.2060802@johnmeinel.com> <415A74EB.9040308@archonet.com> In-Reply-To: <415A74EB.9040308@archonet.com> 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="------------enigF7951A9C532AA07E99D5651C" X-Virus-Scanned: clamd / ClamAV version 0.75, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75, 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: 200409/159 X-Sequence-Number: 2289 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigF7951A9C532AA07E99D5651C Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Richard Huxton wrote: > John Meinel wrote: > >> >> So notice that when doing the actual select it is able to do the index >> query. But for some reason with a prepared statement, it is not able >> to do it. >> >> Any ideas? > > > In the index-using example, PG knows the value you are comparing to. So, > it can make a better estimate of how many rows will be returned. With > the prepared/compiled version it has to come up with a plan that makes > sense for any value. > > If you look back at the explain output you'll see PG is guessing 181,923 > rows will match with the prepared query but only 1 for the second query. > If in fact you returned that many rows, you wouldn't want to use the > index - it would mean fetching values twice. > > The only work-around if you are using plpgsql functions is to use > EXECUTE to make sure your queries are planned for each value provided. > I suppose that make sense. If the number was small (< 100) then there probably would be a lot of responses. Because the tproject table is all small integers. But for a large number, it probably doesn't exist on that table at all. Thanks for the heads up. John =:-> --------------enigF7951A9C532AA07E99D5651C 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 iD8DBQFBWs0iJdeBCYSNAAMRAu/PAJ9j450dzwsMWNUSlR4AvbirOzTlTQCgw4dM 069qTf5ZqQ2fRKsIt/ZkYcA= =n+Tk -----END PGP SIGNATURE----- --------------enigF7951A9C532AA07E99D5651C--