Re: problem with pg_statistics

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?

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

>sort_mem = 32000
>shared_buffers = 13000

Personally I would set them to lower values, but if you have good
reasons ...

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

Servus
Manfred