Grokbase › Groups › PostgreSQL › pgsql-performance › January 2006
FAQ
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:[email protected] - http://www.easter-eggs.com

Search Discussions

  • Mark Lewis at Jan 30, 2006 at 11:26 pm ⇧
    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).
  • Emmanuel Lacour at Jan 31, 2006 at 12:27 am ⇧

    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:[email protected] - http://www.easter-eggs.com
  • Tom Lane at Jan 31, 2006 at 1:55 am ⇧

    Mark Lewis 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
  • Jim Buttafuoco at Jan 30, 2006 at 11:37 pm ⇧
    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 <[email protected]>
    To: [email protected]
    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:[email protected] - http://www.easter-eggs.com

    ---------------------------(end of broadcast)---------------------------
    TIP 6: explain analyze is your friend
    ------- End of Original Message -------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJan 30, '06 at 10:57p
activeJan 31, '06 at 1:55a
posts5
users4
websitepostgresql.org
irc#postgresql

4 users in discussion

Emmanuel Lacour: 2 posts Mark Lewis: 1 post Jim Buttafuoco: 1 post Tom Lane: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase