pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Galbavy <peter.galbavy@knowledge.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: performance of functions - or rather lack of it
Date: Wed, 04 Apr 2001 13:01:15 -0400
Message-ID: <7592.986403675@sss.pgh.pa.us> (raw)
In-Reply-To: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com>
References: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com>

"Peter Galbavy" <peter.galbavy@knowledge.com> writes:
> We are building a postgresql based backend database for our 'hosting
> provisioning' system. In a vain attempt to add some, what I thought, simple
> performance tweaks, I thought I would try putting some of the larger and
> more straighforward queries into functions. For everything else the same,
> the functions are on the whole slower. Should they be ?

Possibly.  In your example, the planner sees dm.instance and mb.instance
being compared to known literal values when you execute the statement
directly, but to unknown values (function parameters) when you use a
function.  This might shift the selectivity estimates enough to result
in choice of a different query plan, which could result in speedup or
slowdown depending on how close to reality the estimates are.

Without knowing which PG version you're using, what plans you're
getting, or even whether you've VACUUM ANALYZEd lately, it's difficult
to say more than that.

> I would have thought that not sending the long SQL across the wire 1000
> times would have saved some time even without any potential query
> optimisations by pre-parsing the SQL ?

Unless your TCP connection is running across tin cans and string,
the transfer time for the query text is negligible ...

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <7592.986403675@sss.pgh.pa.us>
Permalink:  ../7592.986403675@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/7592.986403675@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, peter.galbavy@knowledge.com
  Subject: Re: performance of functions - or rather lack of it
  In-Reply-To: <7592.986403675@sss.pgh.pa.us>
  References: <00bc01c0bcef$c527cf20$6601a8c0@knowledge.com> <7592.986403675@sss.pgh.pa.us>

* 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