pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planner issue
5+ messages / 4 participants
[nested] [flat]
* Query planner issue
@ 2006-01-30 22:57 Emmanuel Lacour <elacour@easter-eggs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Emmanuel Lacour @ 2006-01-30 22:57 UTC (permalink / raw)
To: 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@easter-eggs.com - http://www.easter-eggs.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planner issue
@ 2006-01-30 23:26 Mark Lewis <mark.lewis@mir3.com>
parent: Emmanuel Lacour <elacour@easter-eggs.com>
1 sibling, 2 replies; 5+ messages in thread
From: Mark Lewis @ 2006-01-30 23:26 UTC (permalink / raw)
To: Emmanuel Lacour <elacour@easter-eggs.com>; +Cc: pgsql-performance
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).
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planner issue
@ 2006-01-30 23:37 Jim Buttafuoco <jim@contactbda.com>
parent: Emmanuel Lacour <elacour@easter-eggs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim Buttafuoco @ 2006-01-30 23:37 UTC (permalink / raw)
To: Emmanuel Lacour <elacour@easter-eggs.com>; pgsql-performance
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 -------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planner issue
@ 2006-01-31 00:27 Emmanuel Lacour <elacour@easter-eggs.com>
parent: Mark Lewis <mark.lewis@mir3.com>
1 sibling, 0 replies; 5+ messages in thread
From: Emmanuel Lacour @ 2006-01-31 00:27 UTC (permalink / raw)
To: pgsql-performance
On Mon, Jan 30, 2006 at 03:26:23PM -0800, Mark Lewis wrote:
> 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.
>
Thanks! Vacuum full did it. I will now play with fsm settings to avoid
running a full vacuum daily...
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query planner issue
@ 2006-01-31 01:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Lewis <mark.lewis@mir3.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-01-31 01:55 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Emmanuel Lacour <elacour@easter-eggs.com>; pgsql-performance
Mark Lewis <mark.lewis@mir3.com> writes:
> You really, really want to upgrade as soon as possible,
No, sooner than that. Show your boss the list of known
data-loss-causing bugs in 7.2.1, and refuse to take responsibility
if the database eats all your data before the "in good time" upgrade.
The release note pages starting here:
http://developer.postgresql.org/docs/postgres/release-7-2-8.html
mention the problems we found while 7.2 was still supported. It's
likely that some of the 7.3 bugs found later than 2005-05-09 also
apply to 7.2.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-01-31 01:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-30 22:57 Query planner issue Emmanuel Lacour <elacour@easter-eggs.com>
2006-01-30 23:26 ` Mark Lewis <mark.lewis@mir3.com>
2006-01-31 00:27 ` Emmanuel Lacour <elacour@easter-eggs.com>
2006-01-31 01:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-30 23:37 ` Jim Buttafuoco <jim@contactbda.com>
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