Обсуждение: function call vs staright query
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?
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]
Vincenzo Romano <vincenzo.romano@gmail.com> writes:
> How can I delay the query planner decisions until the actual query is to be
> done inside the function body?
Use plpgsql's EXECUTE. AFAIR there is no way in a SQL-language function.
regards, tom lane
On Monday 19 March 2007 05:07 Tom Lane wrote: > Vincenzo Romano <vincenzo.romano@gmail.com> writes: > > How can I delay the query planner decisions until the actual query is to > > be done inside the function body? > > Use plpgsql's EXECUTE. AFAIR there is no way in a SQL-language function. > > regards, tom lane The body of a function is *always* treated by the planner as if it were a dynamically created query. The fact we all use the "$$"s (or also the 's) around the function body tells it all. The PREPARE requires every session to do it upon connections, because prepared statements are managed on a per-session basis. What I don't understand is why the planner gets passed by during those queries while it is at full steam during the "normal" queries. But this could be due to my ignorance! :-) -- Vincenzo Romano ---- Maybe Computers will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1987]