From: Tom Lane <tgl@sss.pgh.pa.us>
To: CS DBA <cs_dba@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: function slower than the same code in an sql file
Date: Fri, 28 Oct 2011 01:10:08 -0400
Message-ID: <14399.1319778608@sss.pgh.pa.us> (raw)
In-Reply-To: <4EAA3597.3020202@consistentstate.com>
References: <4EAA3597.3020202@consistentstate.com>
CS DBA <cs_dba@consistentstate.com> writes:
> I have code that drops a table, re-create's it (based on a long set of
> joins) and then re-creates the indexes.
> It runs via psql in about 10 seconds. I took the code and simply
> wrapped it into a plpgsql function and the function version takes almost
> 60 seconds to run.
> I always thought that functions should run faster than psql... am I wrong?
Did you really just put the identical queries into a function, or did
you parameterize them with values passed to the function?
Parameterized queries are often slower due to the planner not knowing
the specific constant values that are used in the actual calls. There's
some work done for 9.2 to improve that, but in existing releases you
typically have to construct dynamic queries and EXECUTE them if you run
into this problem.
regards, tom lane
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: tgl@sss.pgh.pa.us, cs_dba@consistentstate.com
Subject: Re: function slower than the same code in an sql file
In-Reply-To: <14399.1319778608@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