Date: Wed, 30 Aug 2006 19:29:35 +1000
From: Matthew Sullivan <matthew@sorbs.net>
Subject: performance problems.
To: pgsql-performance@postgresql.org
Message-id: <44F55A7F.40305@sorbs.net>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.4)
	Gecko/20060516 SeaMonkey/1.0.2

All,

Got a little bit of a performance problem I hope that can be resolved.

All the files/info I believe you are going to ask for are here:

http://www.au.sorbs.net/~matthew/postgres/30.8.06/

The odd thing was it originally was fast (1-2 seconds) which is all I 
need - the query is a permissions check and I added a permissions 
caching engine to the client code.  However, I was testing part of my 
new interface and added and "expired" some rows in the permissions, and 
authorisation tables (taking the row count to ~15) the performance 
dropped to 86seconds (ish) which is unusable... :-(

Unfortunately I do not have a query plan from before the performance issue.

work_mem has been adjusted from 512 to 8192, 65536 and 1000000 with no 
apparent effect.
random_page_cost has been 4 and 2 - 2 results in 89seconds for the query.

The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons and a 
Compaq RAID 3200 in RAID 5 configuration running across 3 spindles (34G 
total space).

The OS is FreeBSD 5.4-RELEASE-p14
The PG Version is 8.1.3

Solutions/tips greatly appreciated.

Regards,

Mat