Re: problem with pg_statistics

On Thu, 26 Jun 2003 12:03:52 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> > On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > wrote:
> >> Try reducing random_page_cost
>
> > With index scan cost being more than 25 * seq scan cost, I guess that
> > - all other things held equal - even random_page_cost = 1 wouldn't
> > help.
>
> Oh, you're right, I was comparing the wrong estimated costs. Yeah,
> changing random_page_cost won't fix it.
>
> > Or there's something wrong with correlation?
>
> That seems like a good bet. Andre, is this table likely to be
> physically ordered by time_stamp, or nearly so? If so, do you
> expect that condition to persist, or is it just an artifact of
> a test setup?
>

First of all thanks for the quick response.

We have three servers at different places, all servers are running
with athlon processors and have ram between 512M up to 1024M,
and a frequency between 700 and 1400Mhz.
All servers running under Linux 7.2 Kernel 2.4.20.
We use this table to collect traffic of our clients.
Traffic data are inserted every 5 minutes with the actual datetime
of the transaction, thatswhy the table should be physically order by time_stamp.
All servers are running in production and i could reproduce the problem on
all three servers.

To answer Manfreds questions:
> Andre, what hardware is this running on? What are the values of
> shared_buffers, random_page_cost, effective_cache_size, ... ? Could
> you show us the result of
>
> SELECT * FROM pg_stats
> WHERE tablename = "tbl_traffic" AND attname = "time_stamp";

The only changes we have made are

sort_mem = 32000
shared_buffers = 13000

All other values are commented out and should be set to default
by postgres itself.

#max_fsm_relations = 100 # min 10, fsm is free space map
#max_fsm_pages = 10000 # min 1000, fsm is free space map

#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025

Hope this help ...

Thanks, as