X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 30D789DCA86 for ; Mon, 30 Jan 2006 19:37:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04493-04 for ; Mon, 30 Jan 2006 19:37:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from amanda.contactbda.com (ipn36372-f65123.cidr.lightship.net [216.204.66.227]) by postgresql.org (Postfix) with ESMTP id 376AA9DC9FA for ; Mon, 30 Jan 2006 19:37:27 -0400 (AST) Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2]) by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id k0UNbM4K004301; Mon, 30 Jan 2006 18:37:22 -0500 From: "Jim Buttafuoco" To: Emmanuel Lacour , pgsql-performance@postgresql.org Reply-To: jim@contactbda.com Subject: Re: Query planner issue Date: Mon, 30 Jan 2006 18:37:22 -0500 Message-Id: <20060130233550.M83895@contactbda.com> In-Reply-To: <20060130225711.GE1320@easter-eggs.com> References: <20060130225711.GE1320@easter-eggs.com> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 192.168.1.1 (jim) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.131 required=5 tests=[AWL=0.131] X-Spam-Score: 0.131 X-Spam-Level: X-Archive-Number: 200601/483 X-Sequence-Number: 16961 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 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 -------