Re: problem with pg_statistics

Поиск
Список
Период
Сортировка
От Andre Schubert
Тема Re: problem with pg_statistics
Дата
Msg-id 20030627080735.66d6bdf0.andre.schubert@km3.de
обсуждение исходный текст
Ответ на Re: problem with pg_statistics  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: problem with pg_statistics  (Manfred Koizar <mkoi-pg@aon.at>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: problem with pg_statistics
Следующее
От: Andre Schubert
Дата:
Сообщение: Re: problem with pg_statistics