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 DA80755F51 for ; Tue, 8 Mar 2005 15:10:43 +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 94930-02 for ; Tue, 8 Mar 2005 15:10:38 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 5AF5F54F2C for ; Tue, 8 Mar 2005 15:10:35 +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 j28FAV319920; Tue, 8 Mar 2005 09:10:31 -0600 Message-ID: <422DC066.10802@arbash-meinel.com> Date: Tue, 08 Mar 2005 09:10:30 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?UTF-8?B?TWFya3VzIEJlcnRoZWF1IOKYrQ==?= Cc: "pgsql-performance@postgresql.org" Subject: Re: pl/pgsql faster than raw SQL? References: <1110284432.4905.18.camel@dicaprio.akademie1.de> In-Reply-To: <1110284432.4905.18.camel@dicaprio.akademie1.de> 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="------------enig60B70C74B37CC3795F87B5AA" 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/129 X-Sequence-Number: 10834 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig60B70C74B37CC3795F87B5AA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Markus Bertheau ☭ wrote: >Hi, I have the following strange situation: > > > ... >oocms=# explain analyze select count(1) from objects where class = 'Picture'; > QUERY PLAN >---------------------------------------------------------------------------------------------------------------- > Aggregate (cost=278.16..278.16 rows=1 width=0) (actual time=44.121..44.123 rows=1 loops=1) > -> Seq Scan on objects (cost=0.00..267.65 rows=4205 width=0) (actual time=0.030..33.325 rows=4308 loops=1) > Filter: ("class" = 'Picture'::text) > Total runtime: 44.211 ms >(записей: 4) > >oocms=# explain analyze select class_get_number_of_objects('Picture'); > QUERY PLAN >-------------------------------------------------------------------------------------- > Result (cost=0.00..0.01 rows=1 width=0) (actual time=27.019..27.022 rows=1 loops=1) > Total runtime: 27.062 ms >(записей: 2) > > >I.e. a function takes 27 ms to do what takes an equivalent piece of sql >43 ms. How can this be explained? > >Some more info: > > In explain analyze, there is a per-row overhead of 2 gettimeofday() calls. This is usually very low and hidden in I/O, but on queries where you go through a lot of rows, but things are cached in ram, it can show up. So the explain analyze is going deep into the SQL query. With a stored procedure, explain analyze only runs the procedure, it doesn't instrument the actual function. So you don't have that per-row overhead. For an alternate accurate view. Try: # \timing # explain analyze select count(1) from objects where class = 'Picture'; # explain analyze select class_get_number_of_objects('Picture'); \timing will also give you the time it takes to run the query, but it doesn't instrument anything. John =:-> --------------enig60B70C74B37CC3795F87B5AA 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 iD8DBQFCLcBmJdeBCYSNAAMRAgghAJ9V78gFK+Bg7Y9LVQVeyGuyQEHSfACfU/Nb UWrd+SLSmfuJ2amIDaNvC88= =AOIb -----END PGP SIGNATURE----- --------------enig60B70C74B37CC3795F87B5AA--