Re: problem with pg_statistics

On Fri, 27 Jun 2003 10:43:01 +0200
Manfred Koizar <mkoi-pg(at)aon(dot)at> wrote:

> On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert
> <andre(dot)schubert(at)km3(dot)de> wrote:
> >Traffic data are inserted every 5 minutes with the actual datetime
> >of the transaction, thatswhy the table should be physically order by time_stamp.
>
> So I'd expect a correlation of nearly 1. Why do your statistics show
> a value of -0.479749? A negative correlation is a sign of descending
> sort order, and correlation values closer to 0 indicate poor
> correspondence between column values and tuple positions.
>
> Could this be the effect of initial data loading? Are there any
> updates or deletions in your traffic table?
>

We dont make updates the traffic table.
Once a month we delete the all data of the oldest month.
And after that a vacuum full verbose analyze is performed.
Could this cause reordering of the data ?
And should i do a cluster idx_ts tbl_traffic ?

> >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";
> ^ ^ ^ ^
> Oops, these should have been single quotes. It's too hot here these
> days :-)
>

You are so right ... :)

> >#effective_cache_size = 1000 # default in 8k pages
>
> This is definitely too low. With 512MB or more I tend to set this to
> ca. 80% of available RAM. Use top and free to find hints for good
> values.
>

Ok, i will talk with my coworker ( he is the sysadmin of our machine )
and look if can use such amount of RAM, because there are several other
processes that are running on these machines.
But i will test and report ...

Thanks, as