2010/4/24 Grzegorz Jaśkiewicz :
>
>
> On Sat, Apr 24, 2010 at 2:23 PM, Merlin Moncure wrote:
>>
>> Well, you missed the most important part: not using cursors at all.
>> Instead of declaring a cursor and looping it to build the array, build
>> it with array(). That's what I've been saying: arrays
On Sat, Apr 24, 2010 at 2:23 PM, Merlin Moncure wrote:
>
> Well, you missed the most important part: not using cursors at all.
> Instead of declaring a cursor and looping it to build the array, build
> it with array(). That's what I've been saying: arrays can completely
> displace both temp tabl
On Fri, Apr 23, 2010 at 10:31 PM, Eliot Gable
wrote:
> In each case, the results are available outside the stored procedure by
> either fetching from the cursor or selecting from the temporary table.
> Clearly, the temporary table takes a performance hit compared using arrays.
> Building an array
FYI, I had a query like this :
(complex search query ORDER BY foo LIMIT X)
LEFT JOIN objects_categories oc
LEFT JOIN categories c
GROUP BY ...
(more joins)
ORDER BY foo LIMIT X
Here, we do a search on "objects" (i'm not gonna give all the details,
they're not interesting for the problem at ha
More benchmarking results are in with a comparison between cursors, arrays,
and temporary tables for storing, using, and accessing data outside the
stored procedure:
CREATE OR REPLACE FUNCTION Test_Init() RETURNS INTEGER AS
$BODY$
DECLARE
temp INTEGER;
BEGIN
DROP TABLE IF EXISTS test_table1
That's a good point. However, even after changing it, it is still 12ms with
the function call verses 6ms without the extra function call. Though, it is
worth noting that if you can make the function call be guaranteed to return
the same results when used with the same input parameters, it ends up b
On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable
wrote:
> And, from these tests, it would be significant overhead.
Yeah, I've been very disappointed by the size of the function-call
overhead on many occasions. It might be worth putting some effort
into seeing if there's anything that can be done abo
On Fri, Apr 23, 2010 at 4:42 PM, Eliot Gable
wrote:
> To answer the question of whether calling a stored procedure adds any
> significant overhead, I built a test case and the short answer is that it
> seems that it does:
>
> CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS
> $BODY$
> DECLARE
To answer the question of whether calling a stored procedure adds any
significant overhead, I built a test case and the short answer is that it
seems that it does:
CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS
$BODY$
DECLARE
temp INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
SELECT
I appreciate all the comments.
I will perform some benchmarking before doing the rewrite to be certain of
how it will impact performance. At the very least, I think can say for
near-certain now that the indexes are not going to help me given the
particular queries I am dealing with and limited num
On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure wrote:
> The timings are similar, but the array returning case:
> *) runs in a single statement. If this is executed from the client
> that means less round trips
> *) can be passed around as a variable between functions. temp table
> requires re
On Wed, Apr 21, 2010 at 4:16 PM, Eliot Gable
wrote:
> I have previously discussed my very long PL/PGSQL stored procedure on this
> list. However, without getting into too many details, I have another
> performance-related question.
ok, here's a practical comparion:
-- test data
create table foo(f
On Thu, Apr 22, 2010 at 8:14 AM, Merlin Moncure wrote:
> This will use an index on bar.bar_id if it exists. Obviously, any
> indexes on foo are not used after creating the array but doesn't
> matter much as long as the right side is indexed. Your cursor method
> does do any better in this regard
On Wed, Apr 21, 2010 at 4:16 PM, Eliot Gable
wrote:
> I have previously discussed my very long PL/PGSQL stored procedure on this
> list. However, without getting into too many details, I have another
> performance-related question.
>
> The procedure currently uses cursors to return multiple result
On Apr 21, 2010, at 1:16 PM, Eliot Gable wrote:
> I have previously discussed my very long PL/PGSQL stored procedure on this
> list. However, without getting into too many details, I have another
> performance-related question.
>
> The procedure currently uses cursors to return multiple result
I think it's really tough to say how this is going to perform. I'd
recommend constructing a couple of simplified test cases and
benchmarking the heck out of it. One of the problems with temporary
tables is that every time you create a temporary table, it creates a
(temporary) record in pg_class;
I have previously discussed my very long PL/PGSQL stored procedure on this
list. However, without getting into too many details, I have another
performance-related question.
The procedure currently uses cursors to return multiple result sets to the
program executing the procedure. Basically, I do
17 matches
Mail list logo
