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

RE: dramatic slowdown in selects after pg has been running for a while

From: patrick(dot)wolf(at)Aerojet(dot)com (WOLF, PATRICK)
To: "'Joe Slag'" <jslag(at)visi(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: RE: dramatic slowdown in selects after pg has been running for a while
Date: 2000-07-21 19:36:35
Message-ID: 63A19D0F08E6D211AD740008C7B1C47B02FE1648@APD-MAIL1 (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Try running vacuum on the table or the database.  Here's an excerpt from the
man on vacuum:


VACUUM serves two purposes in Postgres as both a means to reclaim storage
and also a means to collect information for the optimizer. 

VACUUM opens every class in the database, cleans out records from rolled
back transactions, and updates statistics in the system
catalogs. The statistics maintained include the number of tuples and number
of pages stored in all classes. 

VACUUM ANALYZE collects statistics representing the disbursion of the data
in each column. This information is valuable when several
query execution paths are possible. 

Running VACUUM periodically will increase the speed of the database in
processing user queries. 

Patrick C. Wolf
Test Manager
Socorro Plant

		-----Original Message-----
		From:	Joe Slag [mailto:jslag(at)visi(dot)com]
		Sent:	Friday, July 21, 2000 1:05 PM
		To:	pgsql-novice(at)postgresql(dot)org
		Subject:	[NOVICE] dramatic slowdown in selects after
pg has been running for a while

		I'm evaluating pg for use in my company, and have run into a
bit of a snag.

		One of the tests I've been running is a loop of 10,000
"select *
		from foo" statements from a perl program, where foo is:

		                      Table "foo"
			    Attribute |  Type   | Modifier 
			    bar       | integer | 
			    zag       | text    | 

		When I initially ran this test on my workstation (500 mhz
PIII, 128 meg
		ram, debian 2.2 w/2.2.16 kernel) the whole process took
		10 seconds.  After getting results from my select test, I
did 10,000 
		updates (which took an average of 37 seconds), and then
deleted the rows I'd 
		updated (from psql).

		Now, when I rerun the "select" test (against the same data
that was
		there before the updates), it takes forever - results have
		varied from 300-some seconds to over 700.  

		To make sure that the whole pg process wasn't screwed up, I
created another
		similar table and ran my 10,000 select script against it -
and results are
		back down to 10 seconds.  So, it seems that somewhere in the
process of
		running a bunch of updates to "foo" (and deleteing them)
things have
		become screwed up.

		What could be slowing selects against this table down, and
how would
		I proceed to investigate the matter further?  I've been
reading through
		the pg docs, and haven't seen much performance monitoring
other than
		"explain" (which says exactly the same thing about both the
fast and 
		slow tables).  Is there a log somewhere, or a command that
would further
		show me what's going on?


		Joe Slag
		Wagpaw, inc.


pgsql-novice by date

Next:From: John BurskiDate: 2000-07-21 19:38:24
Subject: pg_dump problem
Previous:From: Alfred PerlsteinDate: 2000-07-21 19:23:09
Subject: Re: dramatic slowdown in selects after pg has been running for a while

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