Thread: 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
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). >
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 -------
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
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