Re: Query performance on session table - Mailing list pgsql-novice

[Please don't top post as it makes the discussion more difficult to
follow.]

On Jun 28, 2007, at 18:05 , Burak Seydioglu wrote:

> Here is the results of VACUUM VERBOSE and a query executed right after
> vacuum is complete. Still 30 sec for 700 rows.

That does seem like a long time. One thing to note is SELECT * FROM
session is most likely *not* what your application is doing: it's
looking up one session at a time. The times you're probably
interested in are of the sort

SELECT *
FROM session
WHERE session_id = ?;

> Do I need to do VACUUM
> FULL?

It might help, but AIUI, all VACUUM FULL does compared to VACUUM is
reclaim space more aggressively. Perhaps VACUUM FULL will mean that
the sequential scan will have fewer pages to visit. (A sequential
scan is used because there's no advantage to using an index as SELECT
* FROM session needs to return every row anyway. However, SELECT *
FROM session WHERE session_id = ? will most likely use an index as
it's just returning a single row.) Others on the list probably have
more experience diagnosing this than I.

> # 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.

Looks like the index had quite a few dead row versions.

> 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.

This relatively huge number of pages (373,746) makes me think that
the rows that are still valid are spread out across a large number of
pages. Perhaps VACUUM FULL will condense them. I don't think you will
need to (or even should) VACUUM FULL frequently. If you're VACUUMing
the table frequently enough (such as by using autovacuum with
settings appropriate for your situation), you shouldn't need to
VACUUM FULL much at all.

> INFO:  "session": truncated 373746 to 147910 pages

And it looks like VACUUM was able to significantly reduce the number
of pages used for session (by over 60%, if I'm reading this correctly).

> 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)

You might want to throw in an ANALYZE session as well, as the
statistics are slightly off (793 estimated rows, but only 708 actual
rows), but I don't think this is a significant part of your
performance issue.

So, my (uneducated) recommendation is try a VACUUM FULL VERBOSE
session, and VACUUM session more frequently than once a day, probably
with autovacuum. Also, you should be looking at your app to see what
queries are actually being run against session and how they perform
rather than using SELECT * FROM session as a benchmark (unless SELECT
* FROM session *is* a critical query for your app). I'm very
interested to hear from others if I've misread this, as I'm pretty
inexperienced at doing so. Comments?

Hope this helps.

Michael Glaesemann
grzm seespotcode net