From: Tom Lane <tgl@sss.pgh.pa.us>
To: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is it normal that functions are so much faster than inline queries
Date: Wed, 31 Mar 2004 10:33:20 -0500
Message-ID: <5019.1080747200@sss.pgh.pa.us> (raw)
In-Reply-To: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be>
References: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be>
"Olivier Hubaut" <olivier@scmbb.ulb.ac.be> writes:
> When I want to execute this set of queries in a function:
> ...
> It takes only 2 seconds.
> But when I tried to do it directly in the psql term (replacing the $1
> value with the same used in the function call), I'm obliged to kill the
> second query after 10 minutes because it's still runnning!
You're presumably getting different plans in the two cases. Usually
we hear complaints about the function case being slower, because the
planner has less information when it has to work with a parameter
instead of a constant. In this case it seems the stupider plan is being
chosen with a constant :-(. You have not shown enough information to
tell why, but I'm wondering about datatype mismatch preventing an index
from being used. What is the declared datatype of the $1 parameter, and
does it match what will be assumed for the unadorned constant?
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, olivier@scmbb.ulb.ac.be
Subject: Re: Is it normal that functions are so much faster than inline queries
In-Reply-To: <5019.1080747200@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