From: Jim Buttafuoco <jim@contactbda.com>
To: Emmanuel Lacour <elacour@easter-eggs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query planner issue
Date: Mon, 30 Jan 2006 18:37:22 -0500
Message-ID: <20060130233550.M83895@contactbda.com> (raw)
In-Reply-To: <20060130225711.GE1320@easter-eggs.com>
References: <20060130225711.GE1320@easter-eggs.com>
with Postgresql 7.2.1 you will need to do BOTH vacuum and reindex and with a table that gets many updates/deletes, you
should run vacuum more than daily.
Both issues have been solved in 8.1.
Jim
---------- Original Message -----------
From: Emmanuel Lacour <elacour@easter-eggs.com>
To: pgsql-performance@postgresql.org
Sent: Mon, 30 Jan 2006 23:57:11 +0100
Subject: [PERFORM] Query planner issue
> 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).
>
> --
> Emmanuel Lacour ------------------------------------ Easter-eggs
> 44-46 rue de l'Ouest - 75014 Paris - France - Métro Gaité
> Phone: +33 (0) 1 43 35 00 37 - Fax: +33 (0) 1 41 35 00 76
> mailto:elacour@easter-eggs.com - http://www.easter-eggs.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@contactbda.com, elacour@easter-eggs.com
Subject: Re: Query planner issue
In-Reply-To: <20060130233550.M83895@contactbda.com>
* 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