Re: Inconsistent performance

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.)