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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to