From: Vincenzo Romano <vincenzo.romano@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: function call vs staright query
Date: Sun, 18 Mar 2007 08:12:48 +0100
Message-ID: <200703180812.49164.Vincenzo.Romano@gmail.com> (raw)
In-Reply-To: <200703180750.54703.Vincenzo.Romano@gmail.com>
References: <200703180750.54703.Vincenzo.Romano@gmail.com>
Hi all again.
I've seen that there has been another post similar to mine:
http://archives.postgresql.org/pgsql-performance/2007-03/msg00166.php
I understand that the query planner has less infos about the query
at the time the function is defined / loaded.
In my case in the query there is a "field like string" expression that seems
to be the performance killer.
If the string is 'SOMETING%' the straight query is fast. While '%SOMETING%'
makes the straight query be as slow as the function.
This thing clears part of the problem. The EXPLAIN actually explains a lot.
But the details should be complete at call time when the pattern string is
known. So at least the first case should have comparable performances for both
the straight query and the function call.
So my previous question becomes:
How can I delay the query planner decisions until the actual query is to be
done inside the function body?
Many thanks again for any hint.
On Sunday 18 March 2007 07:50 Vincenzo Romano wrote:
> Hi all.
>
> I'm experiencing a strange behaviour with 8.1.8 (cannot do upgrades to 8.2
> at the moment).
>
> On a 13+ million rows table I can do a query with results back in less than
> 100 ms. Result is a set of bigint.
> But when I encapsulate that query into an "SQL" function with three
> parameters the results come back in about one minute. The function contains
> just the same query as above.
> Of course there's been no change in indices or even into the table itself
> between the two tests.
>
> I'm almost sure I'm missing something, but have no clue about what!
> Any hint?
--
Vincenzo Romano
----
Maybe Computers will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1987]
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: vincenzo.romano@gmail.com
Subject: Re: function call vs staright query
In-Reply-To: <200703180812.49164.Vincenzo.Romano@gmail.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