Postgresql 8.0.1
$ uname -a
Linux example.example.com 2.4.21-27.0.2.ELsmp #1 SMP Wed Jan 19 01:53:23 GMT 2005 i686 i686 i386 GNU/Linux
Incrementing the loop counter by a factor of 10, from 1000 to 10000 makes the process take more than 100 times longer. (I only saw this happen when I happened upon using a numeric() datatype and then dividing i/100 to avoid overflow. It does not happen without the array and working with other, much larger, arrays of other data types you see no slowdown.)
create or replace function baz()
returns void
language plpgsql
as $func$
declare
a numeric(4,2)[] := '{}';
begin
for i in 1..1000 loop
a[i] := -9.0;
a[i] := a[i] + i/100;
end loop;
return;
end;
$func$;------loop size of 1000
=> explain analyze select baz();
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=1116.873..1116.874 rows=1 loops=1)
Total runtime: 1116.894 ms
(2 rows)
-----loop size of 10000
=> explain analyze select baz();
QUERY PLAN
----------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=134312.457..134312.458 rows=1 loops=1)
Total runtime: 134312.487 ms
(2 rows)
Postgresql recompiled from the source rpm with rpmbuild --target=i686-centos-linux
$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.3/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man -- infodir=/usr/share/info --enable-shared --enable-threads=posix -- disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386- redhat-linux
Thread model: posix
gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-42)
$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.00GHz
stepping : 1
cpu MHz : 2992.800
cache size : 1024 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm nx lm
bogomips : 5976.88
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.00GHz
stepping : 1
cpu MHz : 2992.800
cache size : 1024 KB
physical id : 0
siblings : 2
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm nx lm
bogomips : 5976.88
Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
