Skip site navigation (1) Skip section navigation (2)

Query planner issue

From: Emmanuel Lacour <elacour(at)easter-eggs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query planner issue
Date: 2006-01-30 22:57:11
Message-ID: 20060130225711.GE1320@easter-eggs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)easter-eggs(dot)com   -    http://www.easter-eggs.com

Responses

pgsql-performance by date

Next:From: Mark LewisDate: 2006-01-30 23:26:23
Subject: Re: Query planner issue
Previous:From: Jim C. NasbyDate: 2006-01-30 20:25:24
Subject: Re: Huge Data sets, simple queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group