From: Andre Schubert <andre.schubert@km3.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mkoi-pg@aon.at
Cc: pgsql-performance@postgresql.org
Subject: Re: problem with pg_statistics
Date: Fri, 27 Jun 2003 08:07:35 +0200
Message-ID: <20030627080735.66d6bdf0.andre.schubert@km3.de> (raw)
In-Reply-To: <25814.1056643432@sss.pgh.pa.us>
References: <20030626151515.5fd0faf0.andre@km3.de>
<24083.1056636485@sss.pgh.pa.us>
<h64mfv8quqjc19dv0ufm9oegj6p1rpeipp@4ax.com>
<25814.1056643432@sss.pgh.pa.us>
On Thu, 26 Jun 2003 12:03:52 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Manfred Koizar <mkoi-pg@aon.at> writes:
> > On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl@sss.pgh.pa.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
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.schubert@km3.de, tgl@sss.pgh.pa.us, mkoi-pg@aon.at
Subject: Re: problem with pg_statistics
In-Reply-To: <20030627080735.66d6bdf0.andre.schubert@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>
* 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