John Arbash Meinel wrote:
> How about a quick side track.
> Have you played around with your shared_buffers, maintenance_work_mem,
> and work_mem settings?
I have tried to set shared_buffers to 48000 now but no speedup
(11,098.813 ms third try). The others are still default. I'll see
documentation and will play with the other parameters.
> What version of postgres are you using?
8.0.1
> The above names changed in 8.0,
> and 8.0 also has some perfomance improvements over the 7.4 series.
>
> What is your hardware?
My dev notebook Acer TravelMate 292LMi
$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 9
model name : Intel(R) Pentium(R) M processor 1500MHz
stepping : 5
cpu MHz : 1495.485
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr mce cx8 sep mtrr pge mca cmov
pat clflush dts acpi mmx fxsr sse sse2 tm pbe est tm2
bogomips : 2957.31
$ cat /proc/meminfo
MemTotal: 516136 kB
MemFree: 18024 kB
Buffers: 21156 kB
Cached: 188868 kB
SwapCached: 24 kB
Active: 345596 kB
Inactive: 119344 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 516136 kB
LowFree: 18024 kB
SwapTotal: 1004020 kB
SwapFree: 1003996 kB
Dirty: 4 kB
Writeback: 0 kB
Mapped: 343676 kB
Slab: 18148 kB
CommitLimit: 1262088 kB
Committed_AS: 951536 kB
PageTables: 2376 kB
VmallocTotal: 516056 kB
VmallocUsed: 90528 kB
VmallocChunk: 424912 kB
IDE disc.
# hdparm -Tt /dev/hda
/dev/hda:
Timing cached reads: 1740 MB in 2.00 seconds = 870.13 MB/sec
Timing buffered disk reads: 40 MB in 3.30 seconds = 12.10 MB/sec
> Are you testing this while there is load on the
> system, or under no load.
The load is low. This is few seconds after I have run the EXPLAIN ANALYZE.
# cat /proc/loadavg
0.31 0.51 0.33 1/112 6909
> Are you re-running the query multiple times, and reporting the later
> speeds, or just the first time? (If nothing is loaded into memory, the
> first run is easily 10x slower than later ones.)
The times changes only little. First run was about 13 sec, second about
10 sec, third about 11 sec etc.
> Just some background info. If you have set these to reasonable values,
> we probably don't need to spend much time here, but it's just one of
> those things to check.
Sure you are right. I'll try the other parameters.
>
> John
> =:->
>
Miroslav