pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (9+ messages)  latest in thread

Message-ID: <20030627111058.08f71b14.andre@km3.de>
Permalink:  ../20030627111058.08f71b14.andre@km3.de/
Also on:    postgresql.org/message-id/20030627111058.08f71b14.andre@km3.de

 · 

reply

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