From: Andre Schubert <andre@km3.de>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: tgl@sss.pgh.pa.us
Cc: pgsql-performance@postgresql.org
Subject: Re: problem with pg_statistics
Date: Fri, 27 Jun 2003 11:10:58 +0200
Message-ID: <20030627111058.08f71b14.andre@km3.de> (raw)
In-Reply-To: <4avnfvkjr8q7cl8ac6f01j8rddj4pjlte0@4ax.com>
References: <20030626151515.5fd0faf0.andre@km3.de>
<24083.1056636485@sss.pgh.pa.us>
<h64mfv8quqjc19dv0ufm9oegj6p1rpeipp@4ax.com>
<25814.1056643432@sss.pgh.pa.us>
<20030627080735.66d6bdf0.andre.schubert@km3.de>
<4avnfvkjr8q7cl8ac6f01j8rddj4pjlte0@4ax.com>
On Fri, 27 Jun 2003 10:43:01 +0200
Manfred Koizar <mkoi-pg@aon.at> wrote:
> On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert
> <andre.schubert@km3.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: andre@km3.de, mkoi-pg@aon.at, tgl@sss.pgh.pa.us
Subject: Re: problem with pg_statistics
In-Reply-To: <20030627111058.08f71b14.andre@km3.de>
References: <20030626151515.5fd0faf0.andre@km3.de> <24083.1056636485@sss.pgh.pa.us> <h64mfv8quqjc19dv0ufm9oegj6p1rpeipp@4ax.com> <25814.1056643432@sss.pgh.pa.us> <20030627080735.66d6bdf0.andre.schubert@km3.de> <4avnfvkjr8q7cl8ac6f01j8rddj4pjlte0@4ax.com> <20030627111058.08f71b14.andre@km3.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox