Re: Query planner issue

От: Mark Lewis
Тема: Re: Query planner issue
Дата: ,
Msg-id: 1138663583.28241.96.camel@archimedes
(см: обсуждение, исходный текст)
Ответ на: Query planner issue  (Emmanuel Lacour)
Ответы: Re: Query planner issue  (Emmanuel Lacour)
Re: Query planner issue  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Query planner issue  (Emmanuel Lacour, )
 Re: Query planner issue  (Mark Lewis, )
  Re: Query planner issue  (Emmanuel Lacour, )
  Re: Query planner issue  (Tom Lane, )
 Re: Query planner issue  ("Jim Buttafuoco", )

You have lots of dead rows.  Do a vacuum full to get it under control,
then run VACUUM more frequently and/or increase your FSM settings to
keep dead rows in check.  In 7.2 vacuum is pretty intrusive; it will be
much better behaved once you can upgrade to a more recent version.

You really, really want to upgrade as soon as possible, and refer to the
on-line docs about what to do with your FSM settings.

-- Mark Lewis

On Mon, 2006-01-30 at 23:57 +0100, Emmanuel Lacour wrote:
> Hi everybody,
> I have the following problem, on a test server, if I do a fresh import
> of production data then run
> 'explain analyze select count(*) from mandats;'
> I get this result:
> Aggregate  (cost=6487.32..6487.32 rows=1 width=0) (actual time=607.61..607.61 rows=1 loops=1)
>   ->  Seq Scan on mandats  (cost=0.00..6373.26 rows=45626 width=0) (actual time=0.14..496.20 rows=45626 loops=1)
>   Total runtime: 607.95 msec
> On the production server, if I do the same (without other use of the server), I get:
> Aggregate  (cost=227554.33..227554.33 rows=1 width=0) (actual time=230705.79..230705.79 rows=1 loops=1)
>   ->  Seq Scan on mandats  (cost=0.00..227440.26 rows=45626 width=0) (actual time=0.03..230616.64 rows=45760 loops=1)
>   Total runtime: 230706.08 msec
> Is there anyone having an idea on how yo solve this poor performances? I
> think it is caused by many delete/insert on this table every day, but
> how to solve it, I need to run this qury each hour :(. I run
> vacuum each night, postgresql is unfortunatly 7.2.1 :( (no upgrade
> before 2 or 3 months).

В списке pgsql-performance по дате сообщения:

От: Tom Lane
Сообщение: Re: Query planner issue
Сообщение: Delete me