pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John A Meinel <john@arbash-meinel.com>
To: Markus Bertheau ☭ <twanger@bluetwanger.de>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pl/pgsql faster than raw SQL?
Date: Tue, 08 Mar 2005 09:10:30 -0600
Message-ID: <422DC066.10802@arbash-meinel.com> (raw)
In-Reply-To: <1110284432.4905.18.camel@dicaprio.akademie1.de>
References: <1110284432.4905.18.camel@dicaprio.akademie1.de>

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
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (6+ messages)  latest in thread

Message-ID: <422DC066.10802@arbash-meinel.com>
Permalink:  ../422DC066.10802@arbash-meinel.com/
Also on:    postgresql.org/message-id/422DC066.10802@arbash-meinel.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: john@arbash-meinel.com, twanger@bluetwanger.de
  Subject: Re: pl/pgsql faster than raw SQL?
  In-Reply-To: <422DC066.10802@arbash-meinel.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox