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

Re: Query performance on session table

From: "Burak Seydioglu" <buraks78(at)gmail(dot)com>
To: "Michael Glaesemann" <grzm(at)seespotcode(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query performance on session table
Date: 2007-06-28 23:05:18
Message-ID: 1b8a973c0706281605rc294ef3va7c3c7caccd05dd1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Here is the results of VACUUM VERBOSE and a query executed right after
vacuum is complete. Still 30 sec for 700 rows. Do I need to do VACUUM
FULL?

Thanks,

Burak


# VACUUM VERBOSE session;
INFO:  vacuuming "public.session"
INFO:  index "session_pkey" now contains 854 row versions in 5255 pages
DETAIL:  9212 index row versions were removed.
5028 index pages have been deleted, 5004 are currently reusable.
CPU 0.09s/0.03u sec elapsed 4.65 sec.
INFO:  "session": removed 9212 row versions in 3086 pages
DETAIL:  CPU 0.08s/0.15u sec elapsed 1.47 sec.
INFO:  "session": found 9212 removable, 793 nonremovable row versions
in 373746 pages
DETAIL:  34 dead row versions cannot be removed yet.
There were 2938041 unused item pointers.
0 pages are entirely empty.
CPU 2.28s/0.82u sec elapsed 68.88 sec.
INFO:  "session": truncated 373746 to 147910 pages
DETAIL:  CPU 2.65s/0.51u sec elapsed 37.76 sec.
INFO:  vacuuming "pg_toast.pg_toast_123221198"
INFO:  index "pg_toast_123221198_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  "pg_toast_123221198": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
VACUUM

citizenre=# EXPLAIN ANALYZE SELECT * FROM session;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on session  (cost=0.00..147917.93 rows=793 width=282)
(actual time=27260.391..29186.760 rows=708 loops=1)
 Total runtime: 29187.556 ms
(2 rows)




On 6/28/07, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
>
> On Jun 28, 2007, at 15:26 , Burak Seydioglu wrote:
>
> > I am having performance issues with a table that holds session_data.
> > This table is heavily updated and daily vacuumed.
>
> If it's heavily updated, vacuuming once a day may not be enough. You
> can elect to vacuum a single table.
>
> What's the output of VACUUM VERBOSE session ?It should tell you how
> many dead tuples were removed.
>
> You may also want to consider using autovacuum to help keep your
> session table fairly clean.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-06-29 00:56:19
Subject: Re: Query performance on session table
Previous:From: Michael GlaesemannDate: 2007-06-28 21:38:37
Subject: Re: Query performance on session table

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