Re: How to read query plan

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