Re: Inconsistent performance

On Mon, 15 Sep 2003, scott.marlowe wrote:

> On Mon, 15 Sep 2003, Joseph Bove wrote:
>
> > Stephan,
> >
> > I've run explain analyze a number of times and have gotten results between
> > 5.5 and 7.5 seconds
> >
> > Attached is a typical output
> >
> >   QUERY PLAN
> > -------------------------------------
> >   Aggregate  (cost=9993.92..9993.92 rows=1 width=0)
> >                    (actual time=7575.59..7575.59 rows=1 loops=1)
> > ->  Seq Scan on vetapview  (cost=0.00..9771.34 rows=89034 width=0)
> >                                         (actual time=0.06..7472.20
> > rows=88910 loops=1)
> >   Total runtime: 7575.67 msec
> > (3 rows)
> >
> > The only things changing are the actual time. The costs are constant.
> >
> > The relpages from pg_class for vetapview (the table in question) is 8881.
> >
> > At the end of this message is the exhaustive contents of postgresql.conf.
> > The only settings I have attempted tuning are as follows:
> >
> > tcpip_socket = true
> > max_connections = 100
> > shared_buffers = 5000
> > sort_mem = 8192
> > fsync = false
>
> A couple of things.
>
> 1:  Is there an index on the parts of the query used for the where clause?
> 2:  What is your effect_cache_size set to?  It needs to be set right for
> your postgresql server to be able to take advantage of the kernel's cache
> (i.e. use an index scan when the kernel is likely to have that data in
> memory.)

Sorry, that should be effective_cache_size, not effect_cache_size.  It's
set in 8k blocks and is usually about how much buffer / cache you have
left over after the machines "settles" after being up and running for a
while.  Fer instance, on my server, I show 784992K cache, and 42976K buff
under top, so, that's 827968k/8k=103496 blocks.  Note that if you've
recompiled you may have somehow set block size larger, but installations
with postgresql block sizes ~=8k are pretty uncommon, and you'd know if
you had done that, so it's probably 8k blocks.