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 573C0563D5 for ; Wed, 9 Mar 2005 01:28:17 +0000 (GMT) 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 69567-05 for ; Wed, 9 Mar 2005 01:28:08 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 4C7FC563C1 for ; Wed, 9 Mar 2005 01:28:07 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j291S3326223; Tue, 8 Mar 2005 19:28:04 -0600 Message-ID: <422E511B.40102@arbash-meinel.com> Date: Tue, 08 Mar 2005 19:27:55 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gaetano Mendola Cc: =?UTF-8?B?TWFya3VzIEJlcnRoZWF1IOKYrQ==?= , pgsql-performance@postgresql.org Subject: Re: pl/pgsql faster than raw SQL? References: <1110284432.4905.18.camel@dicaprio.akademie1.de> <422E4948.3090208@bigfoot.com> In-Reply-To: <422E4948.3090208@bigfoot.com> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig0B5D74354F9ECD1724F0A569" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/149 X-Sequence-Number: 10854 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig0B5D74354F9ECD1724F0A569 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Gaetano Mendola wrote: >Markus Bertheau ☭ wrote: > > >>Hi, I have the following strange situation: >> >> > >that is no so strange. I have an example where: > >SELECT * FROM my_view WHERE field1 = 'New'; ==> 800 seconds > >SELECT * FROM my_view; ==> 2 seconds > >the only solution I had was to write a function table with >the second select in a loop that was returnin the row if >the field1 was equal = 'New'. >It's strange but happen. > > > >Regards >Gaetano Mendola > > That sounds more like you had bad statistics on the field1 column, which caused postgres to switch from a seqscan to an index scan, only there were so many rows with field1='New' that it actually would have been faster with a seqscan. Otherwise what you did is very similar to the "nested loop" of postgres which it selects when appropriate. The other issue with views is that depending on their definition, sometimes postgres can flatten them out and optimize the query, and sometimes it can't. Order by is one of the big culprits for bad queries involving views. John =:-> --------------enig0B5D74354F9ECD1724F0A569 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.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCLlEeJdeBCYSNAAMRAt20AJ9vnwejH4/RXNUNFc152sduBtE0PACgmxuf BQ45wBmqOYlJLK3uP72iIp0= =M0Jt -----END PGP SIGNATURE----- --------------enig0B5D74354F9ECD1724F0A569--