Re: Speeding up loops in pl/pgsql function

From: "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speeding up loops in pl/pgsql function
Date: 2011-05-25 22:26:17
Message-ID: 4DDD8209.4020208@lerner.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi, everyone. Merlin wrote:

> let's see the source. I bet we can get this figured out.

Here you go... it looked nicer before I started to make optimizations;
I've gotten it to run about 2x as fast as the previous version, but now
I'm sorta stuck, looking for further optimizations, including possible
use of builtin functions.

Thanks for any suggestions you can offer.

CREATE OR REPLACE FUNCTION translate_octals_into_decimals(bytea_string
BYTEA) RETURNS BYTEA AS $$
DECLARE
bytea_string_length INTEGER := length(bytea_string);
current_substring TEXT := '';
translated_string_array BYTEA[];

output_number INTEGER := 0;
output_number_text TEXT := '';
current_digit TEXT := '';
BEGIN
RAISE NOTICE '[translate_octals_into_decimals] start at %, string of
length %', clock_timestamp(), pg_size_pretty(length(bytea_string));

FOR i IN 1..length(bytea_string) BY 3 LOOP
current_substring := substring(bytea_string from i for 3);

output_number := 0;

FOR j IN 0..(length(current_substring) - 1) LOOP
current_digit := substring(current_substring from
(length(current_substring) - j) for 1);
output_number := output_number + current_digit::integer * (8 ^ j);
END LOOP;

output_number_text = lpad(output_number::text, 3, '0');

IF output_number_text::int = 92 THEN
translated_string_array := array_append(translated_string_array,
E'\\\\'::bytea);
ELSIF output_number_text::int = 0 THEN
translated_string_array := array_append(translated_string_array,
E'\\000'::bytea);
ELSE
translated_string_array := array_append( translated_string_array,
chr(output_number_text::integer)::bytea );
END IF;

END LOOP;

RETURN array_to_string(translated_string_array, '');
END;
$$ LANGUAGE 'plpgsql';

Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Kirkwood 2011-05-25 22:42:15 Re: serveRAID M5014 SAS
Previous Message Mark Kirkwood 2011-05-25 22:24:59 Re: serveRAID M5014 SAS