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